Search for values and substitution

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eugenefoxx
Posts: 33
Joined: Thu Jun 04, 2015 11:59 pm

Search for values and substitution

Post by eugenefoxx »

Please help me write a macro.
If in column A the value in the cell is # N / A then in column B the value in the appropriate cell is substituted with 0. Thanks.
LibreOffice 4.4.0.3 on Windows 8.1
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Search for values and substitution

Post by Zizi64 »

Please help me write a macro.
If in column A the value in the cell is # N / A then in column B the value in the appropriate cell is substituted with 0. Thanks.
Is not enough a FORMULA for you?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Search for values and substitution

Post by FJCC »

I agree that a formula should be used if possible. Can you use

Code: Select all

=IF(ISNA(A2);0;Whatever_is_now_in_B2)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
eugenefoxx
Posts: 33
Joined: Thu Jun 04, 2015 11:59 pm

Re: Search for values and substitution

Post by eugenefoxx »

Thanks.

Code: Select all

=IF(ISNA(A2);0;IF(A2<=0;0;" "))
It remains to include the formula in the macro program in the cycle.
LibreOffice 4.4.0.3 on Windows 8.1
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search for values and substitution

Post by Lupp »

The (implied) suggestion by FJCC was to enter the formula in the first data row in an unused column, and to fill it down as needed.
Why do you insist on writing a "macro" despite the fact that you obviously don't know the basics about the API?

If the macro is unavoidable for unknown reasons:
As soon as you have studied an introduction (parts of Andrew Pitonyak's famous texts e.g.) you will see that the formula is an unneccessary complication if used inside a macro. (You would need a FunctionAccess object e.g.)
You need to get the CellRange containing the data in column A of your sheet and its .DataArray. Then you can also define the respective CellRange in a different column, apply GetDataArray again, rework the data as needed by a bit of pure Basic code, and then apply (pseudocode!):

Code: Select all

secondaryCellRange.SetDataArray(reworkedDataArray)
Concerning the API: See http://www.pitonyak.org/oo.php.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
eugenefoxx
Posts: 33
Joined: Thu Jun 04, 2015 11:59 pm

Re: Search for values and substitution

Post by eugenefoxx »

Thank you. But I excellently apply formulas in a macro with a processing cycle of up to 1000 rows. I'll try to try the DateArray.
LibreOffice 4.4.0.3 on Windows 8.1
Post Reply