View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson Greg Wilson is offline
external usenet poster
 
Posts: 747
Default Formula expected end of statement error, typing formula into cell

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

=IF(A10, "Positive", "Negative")

becomes:

ActiveCell.Formula = "=IF(A10, ""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

" wrote:

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!