Run excel formula through VBA

When you are trying to enter a formula that uses R1C1 notation you need to use FormulaR1C1 instead of Formula.

You mean like this:

Sheets(1).Range("C6" & "C" & LRow1).FormulaR1C1 = "= 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]))"

I tried the above..but its still not working..

If possible, can you help me in re-writing this formula into vba?

=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]))