‘ in Macro for Excel Formula (Excel 2002)

Topic

New Reply

Hi,
I have the following code in a macro and it doesn’t like the quotes I use for defining the cell as BLANK (as opposed to zero)… it works if I put a zero in it instead of the “”. Of course the formula works in Excel, but VBA gets confused because of my quotes… is there another option for telling it if it is BLANK (I don’t want it to be zero), then, etc… I’m needing the actual formula in Excel, so this is why I have it coded the way I do. Any help is always appreciated!
Thanks!!!
Lana

Range(“K9”) = “=VLOOKUP(J9,$AE$2:$AG$13,3,FALSE)”
Range(“L9”) = “=G9*K9”
Range(“M9”) = “=IF(H9=””,””,H9)”
Range(“N9”) = “=IF(G9″”,E9*(1+L9),E9+M9)”
Range(“O9”) = “=N9/D9”

Reply | Quote

Replies

If you want to use a quote " within a quoted string, you must double it: "". As a consequence, you must use """ for "". For example:

Range("M9") = "=IF(H9="""","""",H9)"

You can also test for a cell being really blank by using the ISBLANK function:

Range("M9") = "=IF(ISBLANK(H9),"""",H9)"

Note that if H9 contains a formula that returns "", ISBLANK(H9) will be FALSE. It will only be TRUE if H9 is really empty.

Also note that the formula =IF(H9="","",H9) is equivalent to =H9 so you actually don’t need IF here.

Reply | Quote

This makes sense…thanks so much Hans! Note that my formula was supposed to have H9*K9, so that’s why I had the IF, but good catch though, that would have been silly of me to have it that way, I can see why you mentioned it )
Thanks again Hans!
Lana

Reply | Quote