Excel 2007 – Formula in a macro

Topic

New Reply

Hi,
This one should be easy for me but I’m having a brain cramp… if someone could help me out it would be greatly appreciated. I want the macro to put the following formula into cell AL2 in Excel, but my “” are giving me an error message. I know I’ve done this before, but I can’t remember how I got it to work, nor can I find where I used it before. My actual test macro is below the formula.

=IF(O2″CASHPAY”,”N/A”,IF(SUMIF($N$2:$N$65536,N2,$M$2:$M$65536)=0,”Cleared”,”O/S”))

Sub formula()

‘Add formula to column AL for CASHPAY check status

Sheet5.Select
Range(“AL2”) = “=IF(O2″CASHPAY”,”N/A”,IF(SUMIF($N$2:$N$65536,N2,$M$2:$M$65536)=0,”Cleared”,”O/S”))”

End Sub

Thanks!
Lana

Reply | Quote

Replies

You have to double each quote within the formula, because the formula itself is enclosed in quotes. Otherwise, VBA will think the formula ends at the first quote within the string.

Range(“AL2”).Formula = “=IF(O2″”CASHPAY””,””N/A””,IF(SUMIF($N$2:$N$65536,N2,$M$2:$M$65536)=0,””Cleared””,””O/S””))”

Reply | Quote

Ahh.. yes, I tried everything except that! Thanks Hans!
Lana

Reply | Quote