CODEWITHSUNDEEP
excelvba
Adogen
Adogen

Reputation: 3

Why is my iferror function not working in this case?

Why is my IFERROR function not working? I just want it to return "NOT DONKEYS" if the vlookup returns the error 2024 or #NA.

I have tried merging the two lines together by replacing the "QUANTITY" in the IFERROR line with the whole vlookup function but it still doesn't help. I only have the one workbook open, that's why I don't refer to any workbook in the code. The range for vlookup is only a list of fruits and it doesn't have "DONKEY" as an option.

Sub FRUITS()
    Dim QUANTITY As Variant

   'find quantity of fruits
    QUANTITY = Application.VLookup("DONKEY", Worksheets("Sheet1").Range("FRUITS"), 2, False)

    'return "NO DONKEYS" if QUANTITY returns #NA error
    Worksheets("Sheet1").Cells(5, 5).Value = IfError(QUANTITY, "NO DONKEYS")
End Sub

I expect the IFERROR function to return "NO DONKEYS" into cell (5,5) if vlookup runs into an error.

Upvotes: 0

Views: 120

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

Quick one line IF that replaces the error with NO DONKEYS

Sub FRUITS()
    Dim QUANTITY As Variant

   'find quantity of fruits
    QUANTITY = Application.VLookup("DONKEY", Worksheets("Sheet1").Range("FRUITS"), 2, False)

    'return "NO DONKEYS" if QUANTITY returns #NA error
    If IsError(QUANTITY) Then QUANTITY = "NO DONKEYS"

    Worksheets("Sheet1").Cells(5, 5).Value = QUANTITY
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

There is a different way of trapping #NA errors on VLOOKUP, using If IsError or If Not IsError.

Modified Code

Sub FRUITS()

' Dim QUANTITY As Variant

If Not IsError(Application.VLookup("DONKEY", Worksheets("Sheet1").Range("FRUITS"), 2, False)) Then
    Worksheets("Sheet1").Cells(5, 5).Value = Application.VLookup("DONKEY", Worksheets("Sheet1").Range("FRUITS"), 2, False) ' find quantity of fruits
Else ' in case VLOOKUP doesn't find a match
    Worksheets("Sheet1").Cells(5, 5).Value = "NO DONKEYS" 'return "NO DONKEYS" if QUANTITY returns #NA error
End If

End Sub

Note: you could wrap it up using a With Worksheets("Sheet1") in the beginning of this If, or just set a Worksheet object.

Upvotes: 1

Related Questions