Page 1 of 1

Search for values and substitution

Posted: Sun Jul 29, 2018 5:51 am
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.

Re: Search for values and substitution

Posted: Sun Jul 29, 2018 6:20 am
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?

Re: Search for values and substitution

Posted: Sun Jul 29, 2018 6:23 am
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)

Re: Search for values and substitution

Posted: Sun Jul 29, 2018 9:40 am
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.

Re: Search for values and substitution

Posted: Sun Jul 29, 2018 11:15 am
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.

Re: Search for values and substitution

Posted: Sun Jul 29, 2018 12:20 pm
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.