Help with Conditional Macro Please.

users
#1

Hi,
I need a macro to insert a nominal code which depends on the value in a cell. This is to follow the text contents of two other cells (names) and the date contents of two more cells. The end result will be in one cell with comma separating giving:-

Name1,name2, date1-date2, nominal code

If cell value is 2, then nominal code is 4601
If cell value is 4, then nominal code is 4502
If cell value is 6, then nominal code is 4503
If cell value is 8, then nominal code is 4501

I am working on the easier bits but if anybody has time to help it would be much appreciated.

Budgie

#2

Could you not simply create a table of values with 2, 4, 6, and 8 in one column and 4601 etc. in the other and then use VLOOKUP() in a function to extract what you need? I don't see why a macro would be necessary.

I trust this helps.

Brian Barker

#3

Hi,
I need a macro to insert a nominal code which depends on the value in a
cell. This is to follow the text contents of two other cells (names)
and the date contents of two more cells. The end result will be in one
cell with comma separating giving:-

Name1,name2, date1-date2, nominal code

If cell value is 2, then nominal code is 4601
If cell value is 4, then nominal code is 4502
If cell value is 6, then nominal code is 4503
If cell value is 8, then nominal code is 4501

to convert from 2,4,6,8 to the nominal values, you could use:
=CHOOSE(A1/2;4601;4502;4503;4501)

(where A1 contains the value to be 'converted')

#4

Hi Brian,
OK, looks simpler and though I would give it a try but having loaded my .csv file none of the formula are working. Is there somewhere these can be turned on and off?
Budgie

#5

Do you mean that you see formulae instead of the results of those formulae? If so, remove the tick from Tools | Options... | LibreOffice Calc | View | Display | Formulae.

Note also that if you save your work in its current .csv format, any formulae cannot be saved. Instead, just the current values of results of any formulae will be saved, and the functionality will be lost once you reopen the document. To avoid this, you need to save the document in a spreadsheet format, sensibly LibreOffice's default .ods format.

I trust this helps.

Brian Barker

#6

Hi Brian, many thanks. I worked that out and started over. I have achieved much of what I want with the "easier" bits using CONCATENATE but how do I insert spaces or commas? The resulting text looks dreadful all joined up
I have =CONCATENATE(C2,D2,TEXT(

#7

Hi Brian,
Sorry about last post. Email client (Thunderbird on X64 openSUSE 13.1, with KDE desktop) has just stalled.

I have been working on the easy bits of my problem and have a solution except that the answer looks dreadful as I cannot find out how to insert spaces or commas into results of the formula to get the format presentable. I am using:-

=CONCATENATE(C2,D2,TEXT(E2,"dd/mm/yy"),TEXT(F2,"dd/mm/yy))

I hope I can get the results looking better.

That leaves me with the need to add the "conditional" element but LOOKUP may be the solution as just by chance the elements /2 become 1,2,3,4 as another subscriber has seen.

On last question if I get it all worked out is how may I "freeze" the formula results. I need to do this as I must remove a column used in the formula before exporting as .csv for importation into an accounting programme.

Thanks for all your help so far.
Budgie

#8

I have been working on the easy bits of my problem and have a solution except that the answer looks dreadful as I cannot find out how to insert spaces or commas into results of the formula to get the format presentable. I am using:-

=CONCATENATE(C2,D2,TEXT(E2,"dd/mm/yy"),TEXT(F2,"dd/mm/yy))

I hope I can get the results looking better.

Easy: =CONCATENATE(C2,",",D2," ",...
(Actually, I find the alternative =C2&","&D2&" "&... preferable.)

That leaves me with the need to add the "conditional" element but LOOKUP may be the solution as just by chance the elements /2 become 1,2,3,4 as another subscriber has seen.

I think you are confusing two alternative techniques. His trick enables you to use the CHOOSE() function. You could use VLOOKUP() from a table whatever the key values were.

On last question if I get it all worked out is how may I "freeze" the formula results. I need to do this as I must remove a column used in the formula before exporting as .csv for importation into an accounting programme.

There are various techniques:

o Copy the result column. Paste it back over itself, but using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. In the Paste Special dialogue, ensure that Formulae is not ticked.

o Put the unwanted column on a different sheet (before you create your formulae) - so only the material you eventually want to output is on the single sheet that will be saved to your .csv file.

o Copy the required material to appropriate columns on a new sheet (which you will eventually save as the .csv file). When copying the result column, use Paste Special, this time with both Formulae and Link ticked.

I trust this helps.

Brian Barker

#9

Hi :slight_smile:
I think you can add extra characters in speech-marks can't you? Something
like;

=CONCATENATE(C2," ",D2," ",TEXT(E2,"dd/mm/yy")," ",TEXT(F2,"dd/mm/yy))

Regards from
Tom :slight_smile:

#10

Hi Brian,
OK I now have the spaces and other marks sorted out. Not helped by the "Intelligent" quotation marking in libreoffice. I must find out how to turn off. I used CONCATENATE and CHOOSE and now results look OK.

Before I get round to clearing the working columns as you suggest I have a problem with date format which I have forgotten how I solved.

In short the dates are in columns as such as "18 Sep 2015" without leading comma so they are numbers not text. However I cannot change the format. I think last time I did this I created another column with correct date format and used paste special to copy but this time it does not work. Any suggestions please?

Budgie.

#11

OK I now have the spaces and other marks sorted out. Not helped by the "Intelligent" quotation marking in libreoffice. I must find out how to turn off.

Tools | AutoCorrect Options... | Localized Options: untick Replace. Alternatively, just use Edit | Undo (or Ctrl+Z) immediately the correction occurs.

I used CONCATENATE and CHOOSE and now results look OK. Before I get round to clearing the working columns as you suggest I have a problem with date format which I have forgotten how I solved. In short the dates are in columns as such as "18 Sep 2015" without leading comma ...

Er, apostrophe?

... so they are numbers not text. However I cannot change the format.

If you cannot change the format, they are very likely text, not numbers, and therefore not true, adjustable-format dates. They will arrive as text from a CSV file unless you adjust the column types in the Text Import dialogue.

I think last time I did this I created another column with correct date format and used paste special to copy but this time it does not work. Any suggestions please?

Either:
o Adjust the column type to Date in the Text Import dialogue.
Or:
o Use =DATEVALUE() to derive a proper date value from what you have and format that.
Or:
o Use =TEXT(DATEVALUE(),"xxx") to derive a text version of your date, where "xxx" is the required format.

I trust this helps.

Brian Barker

#12

Hi Brian,
Yes, apostrophe sorry.
DATEVALUE, that was it.
Many thanks once more.
Will report back if problems, else thanks again,
Budgie

#13

In general, it is better to use the built-in methods when ever possible since they will easily stay with the sheet without complaining about contains macros (especially if you share them).

Also, there seems to be an easier learning curve with built-in methods! :slight_smile:

#14

I have completed the conversion of my Access contacts data base to LibreBase. The only problem I have not solved is to get the database to open with my main menu form. This could easlly be set in MS Access ut so far I cannot find the mechanism for doing this in Base. Is rhere a way of doing this? Or do I have to open the data base and manully select the for i want to start with?

Regards

Peter Goggin

#15

Hi Peter,

This question was asked recently. If you go to -
     http://www.libreoffice.org/get-help/nabble/
and search for "How to open a particular form at startup" you will find the
thread.

Noel

#16

Hi, I've tried installing the secfix version of LO to run in parallel with the version I generally use (on Windows 7). The instructions in the link were unclear (or just plain wrong), but I did finally manage to get the installer to complete, apparently successfully, i.e. the installer "wizard" reported that install was OK and the files appear to be there, but when I try to start  it up it crashes with the error
    "Application Error: The application was unable to start correctly (0xc000007b)"
Does anyone have the explanation (and hopefully also the remedy) for this?Thanks,/Gary

#17

Gary Collins wrote

Hi, I've tried installing the secfix version of LO to run in parallel
with the version I generally use (on Windows 7). The instructions in the
link were unclear (or just plain wrong), but I did finally manage to get
the installer to complete, apparently successfully, i.e. the installer
"wizard" reported that install was OK and the files appear to be
there, but when I try to start  it up it crashes with the error
    "Application Error: The application was unable to start correctly
(0xc000007b)"
Does anyone have the explanation (and hopefully also the remedy) for
this?Thanks,/Gary

Gary,

I do hundreds of these installs, and at the moment in addition to current
4.3.3.2 build, I have 16 parallel builds of prior release and TB daily
builds available to use on Windows. So, doing this is second nature to me.

I just reviewed the "in parallel" instructions for Windows builds (Version
3.5.x and newer) on the Wiki:

https://wiki.documentfoundation.org/Installing_in_parallel

Two key points for in parallel loads that should help you sort things:

On Windows builds we use the Microsoft Installer mechanism to perform an
"Administrative" installation of the package with the /A flag. Lots of
general support online for what happens with Administrative installations of
.msi packages. But the important part is that the typical installation does
not actually install anything--it just fully extracts it to the system. And
the step to specify the target directory with TARGETDIR= is very necessary
to do things consistently into a location other than the Download folder.
Personally I set it to a folder named for the build on the C: drive, e.g.
TARGETDIR="C:\LO42363_20140904"

The other aspect, in the User Configuration section of the Wiki, is that the
default extraction sets the program to use an internal LibreOffice variable
$SYSUSERCONFIG that on launch points to your Windows %APPDATA%
directory--and would interfere with content of your actual installation. To
run installs in parallel, in the program directory the bootstrap.ini file is
edited to change the variable to $ORIGIN (i.e. where program is being
launched from) and giving it a relative path "/../Data/settings" to keep
everything on execution within the "parallel" installation.

If you get those to facets correct, things should run well for you. If not,
it is possible that the downloaded .msi installer was corrupt. You can
check that for the release builds by reviewing either the hash values, or
PGP signature of the file you download. The released builds are here:
http://downloadarchive.documentfoundation.org/libreoffice/old/ and drill
down to the Windows folder where hash and signatures are on the "details"
links. Verify your download.

Hope that was clear for you, but post back if still stuck.

Stuart

#18

Hi :slight_smile:
Has anyone had any luck trying to find the thread Noel was referring to?
Is it possible to give a link to specific threads?

Has anyone lese been able to convert a database from Access?

Has anyone been able to set-up a database file so that when it opens it
goes straight to a main form instead of into a design view? Is this
something that might be better done using a form that is made in Writer?

Regards from
Tom :slight_smile: