Run excel formula through VBA

[bagadiamohit]

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:


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">=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]))</code>VBA formula which i have tried:


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sheets(4).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]))"</code>
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.

 

[RoryA]

A sheet doesn't have a formula property, a Range does. You need to specify where on the sheet you are putting the formula.

 

[bagadiamohit]

I have tried your suggestion, but i am still getting the run-time error. Here is what i have tried:

For Rows1 = 6 To Lrow1

Sheets(4).Cells(Rows1, 3).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]))"

Next Rows1


Even i tried this:
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]))"

What am i doing wrong?

 

[Norie]

See my response here.