none
Syntax Error Help RRS feed

  • Question

  • Hey all,

    I am currently having trouble with recording a macro. All I want to do is simply create a macro that inserts a VLOOKUP formula into a cell and that's it, however whenever I run the macro, I get a syntax error. I think the issue boils down to the fact that my formula is very long (probably not the most efficient way of writing it), which is causing issues. Having said that, the formula does indeed work if I copy and paste it manually into a cell and it produces the results I want.

    This is the my formula. It is supposed to VLOOKUP between 6 workbooks and each workbook has 2 sheets and sum the results. Not only that, but obviously any N/A results needs to be changed to a 0, adding more length to the formula. Anyway I apologise in advance for the headache I'm about dawn upon you.

    =SUM(IF(ISNA(SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[wkend.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wkend.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[wkend.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wkend.xml]Away!$A$1:$J$500,4,FALSE)))),0,SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[wkend.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wkend.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[wkend.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wkend.xml]Away!$A$1:$J$500,4,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[mon.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[mon.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[mon.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[mon.xml]Away!$A$1:$J$500,4,FALSE)))),0,SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[mon.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[mon.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[mon.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[mon.xml]Away!$A$1:$J$500,4,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[tue.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[tue.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[tue.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[tue.xml]Away!$A$1:$J$500,4,FALSE)))),0,SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[tue.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[tue.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[tue.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[tue.xml]Away!$A$1:$J$500,4,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[wed.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wed.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[wed.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wed.xml]Away!$A$1:$J$500,4,FALSE)))),0,SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[wed.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wed.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[wed.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[wed.xml]Away!$A$1:$J$500,4,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[thu.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[thu.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[thu.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[thu.xml]Away!$A$1:$J$500,4,FALSE)))),0,SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[thu.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[thu.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[thu.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[thu.xml]Away!$A$1:$J$500,4,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[fri.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[fri.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[fri.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[fri.xml]Away!$A$1:$J$500,4,FALSE)))),0,SUM(IF(ISNA(VLOOKUP("*"&$A2&"*",[fri.xml]Home!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[fri.xml]Home!$A$1:$J$500,4,FALSE)),IF(ISNA(VLOOKUP("*"&$A2&"*",[fri.xml]Away!$A$1:$J$500,4,FALSE)),0,VLOOKUP("*"&$A2&"*",[fri.xml]Away!$A$1:$J$500,4,FALSE)))))

    This is the VBA coding I get when I record the macro

    Sub errortest()
    '
    ' errortest Macro
    '
    
    '
        Range("B2").Select
        ActiveCell.FormulaR1C1 = _
            "=SUM(IF(ISNA(SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wkend.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[wkend.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wkend.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[wkend.xml]Away!R1C1:R500C10,3,FALSE)))),0,SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wkend.xml]Home!R1C1:R500C10,3,FALSE)),"& _
            "P(""*""&RC1&""*"",[wkend.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wkend.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[wkend.xml]Away!R1C1:R500C10,3,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[mon.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[mon.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"& _
            "ml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[mon.xml]Away!R1C1:R500C10,3,FALSE)))),0,SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[mon.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[mon.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[mon.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[mon.xml]Away!R1C1:R500C10,3,FALSE)))),"& _
            "UM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[tue.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[tue.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[tue.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[tue.xml]Away!R1C1:R500C10,3,FALSE)))),0,SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[tue.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*""& _
            "l]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[tue.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[tue.xml]Away!R1C1:R500C10,3,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wed.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[wed.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wed.xml]Away!R1C1:R500C10,3"& _
            "0,VLOOKUP(""*""&RC1&""*"",[wed.xml]Away!R1C1:R500C10,3,FALSE)))),0,SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wed.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[wed.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[wed.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[wed.xml]Away!R1C1:R500C10,3,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP(""*"& _
            "*"",[thu.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[thu.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[thu.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[thu.xml]Away!R1C1:R500C10,3,FALSE)))),0,SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[thu.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[thu.xml]Home!R1C1:R500C10,3,"& _
            "F(ISNA(VLOOKUP(""*""&RC1&""*"",[thu.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[thu.xml]Away!R1C1:R500C10,3,FALSE)))),IF(ISNA(SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[fri.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[fri.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[fri.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&"""& _
            "xml]Away!R1C1:R500C10,3,FALSE)))),0,SUM(IF(ISNA(VLOOKUP(""*""&RC1&""*"",[fri.xml]Home!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[fri.xml]Home!R1C1:R500C10,3,FALSE)),IF(ISNA(VLOOKUP(""*""&RC1&""*"",[fri.xml]Away!R1C1:R500C10,3,FALSE)),0,VLOOKUP(""*""&RC1&""*"",[fri.xml]Away!R1C1:R500C10,3,FALSE)))))""& _
            "Documents\
    End Sub

    I appreciate all the help you can give me. Much obliged.

    Wednesday, August 22, 2012 10:06 AM

All replies

  • From View ->Macros->Record Macro

    After setting the recorder on ,copy the formula from a cell and paste it in another cell.Then stop macro recording.

    Then goto VBE (by Alt+F11) and see what excel has recorded for you.You will get similar type of string.Then you can take the VBA equivalent for the formula and use in your own macro.

    Actually the error may be caused by the double quote,single quotes.

    If it does not solve then pls let us know.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 22, 2012 12:14 PM
    Answerer