VBA IFERROR to VLOOKUP referencing another sheet

Question

I am trying to insert this excel formula into a VBA code:

=IFERROR(VLOOKUP(E2,'Manual Flags'!$F$2:$L$902,7,0),"")

Essentially, my main worksheet named "ContractOrganization" needs to also pull/reference data using vlookup from another worksheet named "Manual Flags" and cells (F2 to L902) and print the result into cell L2

I tried adding a vba code below and I set my worksheets and variables ahead of time to try and clean the code, but nothing is working.

How can I reference another worksheet within a formula in vba using iferror and vlookup?

Sub AddFormula_ManualFlag()

Dim rng As Range
Dim ws1, ws2 As Worksheet
Dim result As String

Set ws1 = ThisWorkbook.Sheets("Manual Flags")
Set ws2 = ThisWorkbook.Sheets("ContractOrganization")
Set rng = ws2.Range("L2")

On Error Resume Next 'add this because if value is not found, vlookup fails, you get 1004
result = "=IFERROR(VLOOKUP(ws2.Range("E2"),ws1.Range("$F$2:$L$902").Value,7,0),"""")"

rng.Formula = result


End Sub

Scott Craner Scott Craner · Accepted Answer · 2018-03-13T15:11:43

first remove the On Error Resume Next, it is not needed when you are putting the formula in the cell itself, only when using Application.WorksheetFunction.Vlookup.

Any vba needs to be removed from the quotes and concatenated with &:

result = "=IFERROR(VLOOKUP(" & ws2.Range("E2").Address(0,0) & "," & ws1.Range("$F$2:$L$902").Address(1,1,,1) & ",7,0),"""")"

VBA IFERROR to VLOOKUP referencing another sheet

1 Answers