CODEWITHSUNDEEP
excelvba
Diego
Diego

Reputation: 123

IfError + vlookup VBA

I am using the following VBA code to test an integer variable.

primerRescateCP = WorksheetFunction.IfError(WorksheetFunction.Match(WorksheetFunction.VLookup("RescateCartera Propia", Range("B:B"), 1, 0), Columns(2), 0), 0)

If the test is true the code runs well, but if it is false I get an "Application-defined or object-defined error" message and do not assing 0 to the variable.

I think the iferror formula is well written, so I can't find the solution.

Any advice should be appreciated :-) Thanks in advance!!

Upvotes: 0

Views: 3084

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

You can't use IfError() that way in VBA, consider:

Sub poiuyt()
    Dim primerRescateCP As Long

    With Application.WorksheetFunction
        On Error Resume Next
            primerRescateCP = .Match(.VLookup("RescateCartera Propia", Range("B:B"), 1, 0), Columns(2), 0)
        On Error GoTo 0
        If Err.Number > 0 Then
            primerRescateCP = 0
            Err.Number = 0
        End If
        MsgBox primerRescateCP
    End With
End Sub

EDIT#1:

An update based on the comments:

Sub poiuyt_2()
    Dim primerRescateCP As Long

    With Application.WorksheetFunction
        primerRescateCP = 0
        On Error Resume Next
            primerRescateCP = .Match(.VLookup("RescateCartera Propia", Range("B:B"), 1, 0), Columns(2), 0)
        On Error GoTo 0
        MsgBox primerRescateCP
    End With
End Sub

Upvotes: 2

YowE3K
YowE3K

Reputation: 23974

To avoid using On Error statements, you can test for the error yourself:

Dim resultVLookup As Variant
Dim resultMatch As Variant
Dim primerRescateCP As Variant
primerRescateCP = 0
resultVLookup = Application.VLookup("RescateCartera Propia", Range("B:B"), 1, 0)
If Not IsError(resultVLookup) Then
    resultMatch = Application.Match(resultVLookup, Columns(2), 0)
    If Not IsError(resultMatch) Then
        primerRescateCP = resultMatch
    End If
End If

Upvotes: 1

Excelosaurus
Excelosaurus

Reputation: 2849

Here's my take on it. I gave @Gary's Student a full 20 minutes to get his act together ;-)

Sub Test()
    Dim primerRescateCP As Long

    'Initialize the result to whatever it should be in case of error.
    primerRescateCP = 0

    'Attempt to get the actual value.
    'If the attempt fails, it will be on the right hand side, before the assignment,
    'leaving the variable untouched.
    With Application.WorksheetFunction
        On Error Resume Next
        primerRescateCP = .Match(.VLookup("RescateCartera Propia", Range("B:B"), 1, 0), Columns(2), 0)
        On Error GoTo 0
    End With

    'Display the result.
    MsgBox primerRescateCP
End Sub

Upvotes: 1

Related Questions