Search for values and substitution

Keyboard macros or custom scripts

Search for values and substitution

Postby eugenefoxx » Sun Jul 29, 2018 5:51 am

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
eugenefoxx
 
Posts: 33
Joined: Thu Jun 04, 2015 11:59 pm

Re: Search for values and substitution

Postby Zizi64 » Sun Jul 29, 2018 6:20 am

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; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
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.
User avatar
Zizi64
Volunteer
 
Posts: 7908
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Search for values and substitution

Postby FJCC » Sun Jul 29, 2018 6:23 am

I agree that a formula should be used if possible. Can you use
Code: Select all   Expand viewCollapse view
=IF(ISNA(A2);0;Whatever_is_now_in_B2)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7128
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Search for values and substitution

Postby eugenefoxx » Sun Jul 29, 2018 9:40 am

Thanks.
Code: Select all   Expand viewCollapse view
=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
eugenefoxx
 
Posts: 33
Joined: Thu Jun 04, 2015 11:59 pm

Re: Search for values and substitution

Postby Lupp » Sun Jul 29, 2018 11:15 am

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   Expand viewCollapse view
secondaryCellRange.SetDataArray(reworkedDataArray)


Concerning the API: See http://www.pitonyak.org/oo.php.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2405
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search for values and substitution

Postby eugenefoxx » Sun Jul 29, 2018 12:20 pm

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
eugenefoxx
 
Posts: 33
Joined: Thu Jun 04, 2015 11:59 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests