Run excel formula through VBA

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.

Oops! That was a small one. Somehow, i missed it out. Thanks Noorie.

Now, the code is working fine. But I got a new problem now.

Is there anything wrong with the ""address"" which i have mentioned in the formula?

Because whenever i am trying to run the macro, a popup is being opened and asking for File names.

I am pasting my entire code here:

Public Sub hyperlinks()

Sheets(1).Select
Sheets(1).Range("C6").Select
LRow1 = Sheets(1).UsedRange.Rows.Count
Sheets(1).Range("C6" & ":" & "C" & LRow1).Select
Selection.Copy
Sheets(4).Select
Sheets(4).Range("I1").PasteSpecial xlPasteValues

Sheets(2).Select
Sheets(2).Range("B4").Select
Lrow2 = Sheets(2).UsedRange.Rows.Count
Sheets(2).Range("B4" & ":" & "B" & Lrow2).Select
Selection.Copy
Sheets(4).Select
Sheets(4).Range("J1").PasteSpecial xlPasteValues

'Dim Rows As Integer
Lrow3 = Sheets(4).Range("I1").End(xlUp).Row
For Rows1 = 1 To Lrow3
Sheets(1).Range("C" & Lrow3).FormulaR1C1 = "= IF(ISERROR(VLOOKUP(Sheet4!R" & Lrow3 & "C9,Sheet2!R4C2:R" & Lrow2 & "C2,1,FALSE)),Sheet4!R" & Lrow3 & "C9,HYPERLINK(CELL(""address"",INDEX(Sheet2!R4C2:" & "R" & Lrow2 & "C2,MATCH(Sheet4!R" & Lrow3 & "C9,Sheet2!R4C2:" & "R" & Lrow2 & "C2,0))),Sheet4!R" & Lrow3 & "C9))"
Next Rows1

End Sub