Formula does not work when used in VBA code

mike703 · #1 (**permalink**) 03-31-11, 12:55

OK...so i'm a little perplexed at this point.

Trying to replicate a worksheet using VBA code to build and I have run into a problem. The following line causes an "application-defined or object-defined error":

Range("AA3").Formula = "=IF(X3 ="",IF(W3="",N3+V3,N3+W3), N3+X3)"

What is really bothering me is the fact that the formula is the exact same formula in the cell of the sheet that I am trying to replicate. If I copy and pasted this formula into a cell it would work as intented, but in the line above, it generates an error. Is there something that i'm missing here?

Colin Legg · #2 (**permalink**) 03-31-11, 13:45

Hi Mike,

In VBA, the " character denotes a string so, to include a " in a string, you have to do it like this:

Code:

Range("AA3").Formula = "=IF(X3 ="""",IF(W3="""",N3+V3,N3+W3), N3+X3)"

(or you can use Chr 34 to represent them)

Code:

Range("A3").Formula = "=IF(X3 =" & Chr$(34) & Chr$(34) & ",IF(W3=" & Chr$(34) & Chr$(34) & ",N3+V3,N3+W3), N3+X3)"

mike703 · #3 (**permalink**) 03-31-11, 13:53

That did it! Thanks!!

Internet Services

Web Development

Digital Marketing

Consumer Tech