Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default Formula expected end of statement error, typing formula into cell as part of VBA macro

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!

  #2   Report Post  
Posted to microsoft.public.excel.programming
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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula within a Macro not working as expected. Pank New Users to Excel 2 March 7th 07 11:31 AM
Micrsoft Visual Basic Compile error: Expected: end of statement IJ Excel Discussion (Misc queries) 5 October 23rd 06 12:08 AM
Macro to evaluate a cell and replace one part of the formula John[_114_] Excel Programming 2 January 19th 06 08:31 PM
changing cell formula part of macro thephoenix12[_6_] Excel Programming 1 August 12th 05 10:00 PM
keep formula after typing in a value in a excel cell Henning Voitz Excel Programming 0 February 23rd 04 11:58 AM


All times are GMT +1. The time now is 02:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"