I am trying to VLOOKUP some value in 2 sheets and if the value is found then i am putting a hyperlink on the Sheet 1 cell to point it to the Sheet 2 cell.
I have written an Excel formula for the same and it is working fine. But i am unable to convert it into a VBA formula. I tried many combinations, but i am unable to understand what i am doing wrong.
Excel formula:


=IF(ISERROR(VLOOKUP(RC[7],Sheet2!R1C1:R20C1,1,FALSE)),RC[7],HYPERLINK(CELL("address",INDEX(Sheet2!R1C1:R20C1,MATCH(RC[7],Sheet2!R1C1:R20C1,0))),RC[7]))

VBA formula which i have tried:

Sheets(4).Range("C6" & "C" & LRow1).Formula ="= IF(ISERROR(VLOOKUP(RC[7],Sheet2!R4C2:R"& Lrow2 &"C2,1,FALSE)),RC[7],HYPERLINK(CELL("&"""address"""&",INDEX(Sheet2!R4C2:"&"R"& Lrow2 &"C2,MATCH(RC[7],Sheet2!R4C2:"&"R"& Lrow2 &"C2,0))),RC[7]))"
P.S.: Do not worry about the row and column indexes. I wrote the formula for test file and writing the vba for the master file.