+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1

    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. #2
    Greg Wilson
    Guest

    RE: Formula expected end of statement error, typing formula into cell

    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

    "[email protected]" 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1