CODEWITHSUNDEEP
vbaexcel
Mohit Bagadia
Mohit Bagadia

Reputation: 33

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. What am I 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).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.

Upvotes: 0

Views: 5146

Answers (2)

Rory
Rory

Reputation: 34035

In addition to a range, you need to use the .FormulaR1C1 property if you are going to use R1C1 references:

Sheets(4).Range("A7").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]))"

Upvotes: 0

Manivannan KG
Manivannan KG

Reputation: 371

Just a minor mistake you've done.

Sheets(4).Formula = "" represents the formula applied on sheet4.Logically sheet 4 has almost million rows. Where will the formula sit?

Sheet(4).cells(row, column).Formula = ""

Here is one example i have just wrote few min back for sumif

  shPivotAdjustmentsIRSPV.Cells(NumRows, NumColumns + 1).Formula =
 "=Sum(" & shPivotAdjustmentsIRSPV.Cells(3, NumColumns + 1).Address &
 ":" & shPivotAdjustmentsIRSPV.Cells(NumRows - 2, NumColumns +
 1).Address & ")"

Upvotes: 1

Related Questions