[SOLVED] Trying to use an & in a formula through VBA - Error

  • I am attempting to use an & in a formula through VBA but I keep getting the 1004 Application-defined error. I recorded this line to get the syntax but I can't get it to work! Any help would be much appreciated.


    Recorded code:

    Code
    ActiveCell.FormulaR1C1 = _        "=IF(RC[-4]=""LOGON"",IF(RC[-3]<strLogon1,""YES"",IF(RC[-2]>strLogon2,""YES"","""")),"""")&IF(RC[-4]=""LUNCH"",IF(RC[-1]>strLunch,""YES"",""""),"""")&IF(RC[-4]=""BREAK"",IF(RC[-1]>strBreak,""YES"",""""),"""")&IF(RC[-4]=""LOFF"",IF(RC[-1]>1,""YES"",""""),"""")&IF(RC[-4]=""CPE"",IF(RC[-2]>strCPE,""YES"",""""),"""")&IF(RC[-4]=""MTNG"",IF(RC[-2]>strMeeting,""YES"","""")," & _
            "(RC[-4]=""FUEL"",IF(RC[-1]>strFuel,""YES"",""""),"""")&IF(RC[-4]=""TRNG"",""YES"","""")&IF(RC[-4]=""AVAIL"",IF(RC[-1]>strAvail,""YES"",""""),"""")&IF(RC[-4]=""ASSIST"",""YES"","""")&IF(RC[-4]=""VEHICLE"",""YES"","""")&IF(RC[-4]=""ONHOLD"",IF(RC[-1]>strHold,""YES"",""""),"""")"



    I tried to make edits to the code to get it to work, this one also gets the same error.


    Code
    Range("G2").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(RC[-4]=""LOGON"",IF(RC[-3]<""strLogon1"",""YES"",IF(RC[-2]>""strLogon2"",""YES"","""")),"""")" & Chr(38) & "IF(RC[-4]=""LUNCH"",IF(RC[-1]>""strLunch"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""BREAK"",IF(RC[-1]>""strBreak"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""LOFF"",IF(RC[-1]>1,""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""CPE"",IF(RC[-2]>""strCPE"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""MTNG"",IF(RC[-2]>""strMeeting"",""YES"",""""),(RC[-4]=""FUEL"",IF(RC[-1]>strFuel,""YES"",""""),"""")" & _
            Chr(38) & "IF(RC[-4]=""TRNG"",""YES"","""")" & Chr(38) & "IF(RC[-4]=""AVAIL"",IF(RC[-1]>""strAvail"",""YES"",""""),"""")" & Chr(38) & "IF(RC[-4]=""ASSIST"",""YES"","""")" & Chr(38) & "IF(RC[-4]=""VEHICLE"",""YES"","""")" & Chr(38) & "IF(RC[-4]=""ONHOLD"",IF(RC[-1]>""strHold"",""YES"",""""),"""")"
        Range("G3").Select

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!