VBA vlookup iferror formula error '1004'

Question

There is a code I have been trying to adjust for taking into account IfError for value not found by vlookup function. The requirement is to transfer formula into Excel spreadsheet. Fo you have any idea why it doesn't work? Thank you, Russ

Sub Vlookup_Condition_Formula()
    Dim rng As Range
    Dim i As Long

    Application.ScreenUpdating = False

    Worksheets("Summary").Activate

    'Identify the Destination location to start populating vlookuped values

    Range("C2").Activate

    With Worksheets("Summary").Cells
        Set rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)

        For i = 2 To rng.Rows.Count
            rng.Cells(i, 3).Formula = "=IFERROR((VLookup(" & .Cells(i, 1). _
            Address & "," & "'" & Sheets("FinancePartnerList").Name _
            & "'!A:B," & "2, " & "False), ""Not in Exception List"")"

        Next

    End With

    Application.ScreenUpdating = True

End Sub

Vasily Ivoyzha Vasily Ivoyzha · Accepted Answer · 2015-01-11T08:14:50

The problem is that you have an extra "(" in the formula, remove it and error will disapear. Below three variants of your code, the first variant, in my point of view, is more readable (in case when you need only values then better to use worksheetfunction)

'==================================================================
'another one variant with same output result
'using worksheetfunction.vlookup (without formulas)
Sub Vlookup_Condition()
    Dim Rcnt&, i&, SourceRng$
    On Error Resume Next
    Application.ScreenUpdating = False
    Worksheets("Summary").Activate
    SourceRng = Range("A:B").Address
    With Worksheets("Summary")
    Rcnt = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 2 To Rcnt
            .Cells(i, 3).Value = WorksheetFunction.VLookup(.Cells(i, 1).Value, _
                                 Worksheets("FinancePartnerList").Range(SourceRng), 2, 0)
            If Err.Number > 0 Then
                .Cells(i, 3).Value = "Not in Exception List": Err.Clear
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub
'==================================================================
'your updated variant
'using formulas
Sub Vlookup_Condition_Formula1()
    Dim Rcnt&, i&
    Application.ScreenUpdating = False
    Worksheets("Summary").Activate
    With Worksheets("Summary")
        Rcnt = Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To Rcnt
            Cells(i, 3).Formula = "=IFERROR(VLookup(" & .Cells(i, 1).Address _
                                        & "," & "'" & Sheets("FinancePartnerList").Name & _
                                        "'!A:B," & "2, 0), ""Not in Exception List"")"
        Next
    End With
    Application.ScreenUpdating = True
End Sub
'==================================================================
'your updated variant
'using Evaluate(formulas)
Sub Vlookup_Condition_2()
    Dim Rcnt&, i&
    Application.ScreenUpdating = False
    Worksheets("Summary").Activate
    With Worksheets("Summary")
        Rcnt = Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To Rcnt
            Cells(i, 3).Value = Evaluate("=IFERROR(VLookup(" & .Cells(i, 1).Address _
                                        & "," & "'" & Sheets("FinancePartnerList").Name & _
                                        "'!A:B," & "2, 0), ""Not in Exception List"")")
        Next
    End With
    Application.ScreenUpdating = True
End Sub

VBA vlookup iferror formula error '1004'

1 Answers