VBA Error 1004 when trying to put equals sign in cell value

[YungRichSavage]

Im trying to do something like this

"om" is the if formula but in my language

Range("f3").Value = "=OM(+'1'!H3=0;"";+'1'!H3)"

and for some reason i get the 1004 application or object defined error

when i do this the first line of code works but the same error appers on the next line, why?

Range("f3").Value = "OM(+'1'!H3=0;"";+'1'!H3)"
Range("f3").Value = "=" & Range("f3").Value

 

[Sixthsense]

Change this

Range("f3").Value = "=" & Range("f3").Value

To

Range("f3").Value = Range("f3").Formula

 

[steve the fish]

Hi. When you say this works:

Range("f3").Value = "OM(+'1'!H3=0;"";+'1'!H3)"

Take a look at it on the spreadsheet. You will notice that the pair of inverted commas, "", is no longer "" but ". For that to work correctly in a string you need to use """" not "".

 

[MrIfOnly]

You can also use:

Range("f3").Value = "=OM(+'1'!H3=0;" & Chr(34) & Chr(34) & ";+'1'!H3)"

Regards,

CJ

 

[YungRichSavage]

Still doesn't work,

Range("f3").Value = "OM(+'1'!H3=0;"""";+'1'!H3)"
        Range("f3").Value = Range("f3").Formula

still just appears as a string, no equals sign or anything

I have to fill about 4000 cells with formulas so really want to figure this out

 

[YungRichSavage]

and i still get the same error if i use chr() and everything. It just hates the equals sign

 

[YungRichSavage]

Both of theese genreate the error

and this adds ' instead of =

 

[steve the fish]

Try using commas instead of semi colons. I cant test as i dont use a foreign language version.

Range("f3").Formula = "=OM(+'1'!H3=0,"""",+'1'!H3)"

Other than that record putting the formula in the cell and look what the recorder produces.

 

[YungRichSavage]

Ok thanks recording the macro solved it.
I had to use IF instead of OM

Should have figured that out earlier