Complicated command that is working on a cell not working on VBA

[Aldaron47]

Hello guys

I have the following command that I managed to get it working on a single cell

=IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week1[Email Address],1,FALSE))),"06/11/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week2[Email Address],1,FALSE))),"13/11/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week3[Email Address],1,FALSE))),"20/11/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week4[Email Address],1,FALSE))),"27/11/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week5[Email Address],1,FALSE))),"04/12/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week6[Email Address],1,FALSE))),"11/12/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week7[Email Address],1,FALSE))),"18/12/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week8[Email Address],1,FALSE))),"25/12/2017", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week9[Email Address],1,FALSE))),"01/01/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week9[Email Address],1,FALSE))),"01/01/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week10[Email Address],1,FALSE))),"08/01/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week11[Email Address],1,FALSE))),"15/01/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week12[Email Address],1,FALSE))),"22/01/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week13[Email Address],1,FALSE))),"29/01/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week14[Email Address],1,FALSE))),"05/02/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week15[Email Address],1,FALSE))),"12/02/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week16[Email Address],1,FALSE))),"19/02/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week17[Email Address],1,FALSE))),"26/02/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week18[Email Address],1,FALSE))),"05/03/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week19[Email Address],1,FALSE))),"12/03/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week20[Email Address],1,FALSE))),"19/03/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week21[Email Address],1,FALSE))),"26/03/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week22[Email Address],1,FALSE))),"02/04/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week23[Email Address],1,FALSE))),"09/04/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week24[Email Address],1,FALSE))),"16/04/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week025[Email Address],1,FALSE))),"23/04/2018", IF(NOT(ISERROR(VLOOKUP(F2,DataExtract.xlsx!Week26[Email Address],1,FALSE))),"30/04/2018","Something is Wrong")))))))))))))))))))))))))))

The command is multiple ifs not looking in tabs of a workbook for a specific value and return a date to a cell of another workbook.
I am trying to convert it in working with a MACRO. I have tried to auto record it, or write it myself but until now I have managed to reach this code

    ActiveCell.FormulaR1C1 = _
        "=IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week1[Email Address],1,FALSE))),""06/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week2[Email Address],1,FALSE))),""13/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week3[Email Address],1,FALSE))),""20/11/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week4[Email Address],1,FALSE)))," & _
        """27/11/2017"",IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week5[Email Address],1,FALSE))),""04/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week6[Email Address],1,FALSE))),""11/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week7[Email Address],1,FALSE))),""18/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week8[Email Address],1,FALSE)))," & _
        """25/12/2017"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week9[Email Address],1,FALSE))),""01/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week9[Email Address],1,FALSE))),""08/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week10[Email Address],1,FALSE))),""15/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week11[Email Address],1,FALSE)))," & _
        """22/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week12[Email Address],1,FALSE))),""29/01/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week13[Email Address],1,FALSE))),""05/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week14[Email Address],1,FALSE))),""12/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week15[Email Address],1,FALSE)))," & _
        """19/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week16[Email Address],1,FALSE))),""16/02/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week17[Email Address],1,FALSE))),""05/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week18[Email Address],1,FALSE))),""12/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week19[Email Address],1,FALSE)))," & _
        """19/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week20[Email Address],1,FALSE))),""26/03/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week21[Email Address],1,FALSE))),""02/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week22[Email Address],1,FALSE))),""09/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week23[Email Address],1,FALSE))), & _
        """16/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week24[Email Address],1,FALSE))),""23/04/2018"", IF(NOT(ISERROR(VLOOKUP(RC[-2],DataExtract.xlsx!Week025[Email Address],1,FALSE))),""30/04/2018"","" ""))))))))))))))))))))))))))"

The error i get is a syntax error.

How can I get it to work?

 

[NdNoviceHlp]

Application.WorksheetFunction.VLOOKUP

"RC[-2]" ??????
HTH. Dave

 

[Aldaron47]

At the of position of RC[-2] was the F2 but when I auto recorded the macro it was automatically changed. RC from what I understood is the column or cell where the function was implemented and the -2 means that it will read 2 cells earlier than that cell or column. therefore because the function is located to H2 column, H-2 is the F2 cell