Formula expected end of statement error, typing formula into cell as part of VBA macro

D

DavidGMullins

I'm getting multiple errors trying to get this formula into a macro:
The first being an expected end of statement on the fourth comma of the
third line (at the end of the first If(OR statement)

Basically, I'm just trying to get the formula typed in to the cell as
part of a macro and may be missing an easier way to type it in. The
formula itself works fine if I manually type it into a cell. The macro
recorder says it cannot record it. Please help.

Range("AG2").Select
ActiveCell.Formula = "=AI2&IF(OR(AI2="",AJ2=""),"",", ")&AJ2&
IF(OR(AI2&AJ2="",AK2=""),"",", ")&AK2 & IF(OR(AI2 & AJ2 &
AK2="",AL2=""),"",", ")
& AL2 & IF(OR(AI2 & AJ2 & AK2 & AL2="",AM2=""),"",", ")
&AM2&IF(OR(AI2&AJ2&AK2&AL2&AM2="",AN2=""),"",", ") & AN2
& IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2="",AO2=""),"",", ")
& AO2 & IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2 &
AO2="",AP2=""),"",", ")
& AP2"

Thanks!
 
G

Greg Wilson

Quotations within quotations have to be doubled. For example, the formula:

=IF(A1>0, "Positive", "Negative")

becomes:

ActiveCell.Formula = "=IF(A1>0, ""Positive"", ""Negative"")"

This holds for empty quotations also ("" becomes """"). Correct word wrap.

ActiveCell.Formula = "=AI2&IF(OR(AI2="""",AJ2=""""),"""","",
"")&AJ2&IF(OR(AI2&AJ2="""",AK2=""""),"""","", "")&AK2 & IF(OR(AI2 & AJ2
&AK2="""",AL2=""""),"""","", "")& AL2 & IF(OR(AI2 & AJ2 & AK2 &
AL2="""",AM2=""""),"""","",
"")&AM2&IF(OR(AI2&AJ2&AK2&AL2&AM2="""",AN2=""""),"""","", "") & AN2&
IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2="""",AO2=""""),"""","", "")& AO2 &
IF(OR(AI2 & AJ2 & AK2 & AL2 & AM2 & AN2 &AO2="""",AP2=""""),"""","", "")& AP2"

Regards,
Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top