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.
Search for values and substitution
-
- Posts: 33
- Joined: Thu Jun 04, 2015 11:59 pm
Search for values and substitution
LibreOffice 4.4.0.3 on Windows 8.1
Re: Search for values and substitution
Is not enough a FORMULA for you?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.
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.
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.
Re: Search for values and substitution
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 33
- Joined: Thu Jun 04, 2015 11:59 pm
Re: Search for values and substitution
Thanks.
It remains to include the formula in the macro program in the cycle.
Code: Select all
=IF(ISNA(A2);0;IF(A2<=0;0;" "))
LibreOffice 4.4.0.3 on Windows 8.1
Re: Search for values and substitution
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!):
Concerning the API: See http://www.pitonyak.org/oo.php.
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)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 33
- Joined: Thu Jun 04, 2015 11:59 pm
Re: Search for values and substitution
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