Using a macro to put a function in a certain cell (runtime error 1004)

[Juhanen II]

Hi all,

I have problems using a macro to define the function of let's say cell A5.

I want to put the following function in the cell A5. I have tested this function itself and it works

=IF(ISERROR(VLOOKUP($A4;INDIRECT($C$3);1;FALSE));" ";VLOOKUP($A4;INDIRECT($C$3&"2");2;FALSE))

I use the following macro, but I get the runtime error 1004 (Application-defined or object defined error)

Please Login or Register  to view this content.

I really don't understand what I'm doing wrong

Thank you all,
-Juhanen-

[VBA Noob]

Hi,

Try

Please Login or Register  to view this content.

VBA Noob

[Juhanen II]

Hi,

Still the same error

Thank you anyway,

-Juhanen-

[Juhanen II]

I'm still struggling with the same problem. Now it seems that the problem is to use a macro to put an IF function to certain cell. Let me explain this via couple simplified examples:

If I use for example this code it works

Please Login or Register  to view this content.

...but this one doesn't (I have changed the SUM function to IF function)

Please Login or Register  to view this content.

So now I have no clue why is this..

Thank you all,
-Juhanen-

[VBA Noob]

Try entering the formula as normal in a cell. Then turn on the macro recorder and enter a ' in front of the equals. Then view the recorded code will look like

Please Login or Register  to view this content.

then remove the R1C1 ref and remove the ' e.g

Please Login or Register  to view this content.

VBA Noob

[Juhanen II]

Now it works,

For some reason I was forced to use FunctionR1C1 instead of Function though, so just had to record and trim it a bit.

Anyways, thank you VBA noob, without your help i would still be hitting my head against the wall.

[VBA Noob]

No problem. Glad it helped

VBA Noob