How to use vlookup in vba-excel?

Question

I want to do a vlookup and after finding a value (which will be 1 or 0), I want to put a condition: if it is 1, divide some values of the worksheet by 100.

When executing this code, the error '1004' appears: Unable to get the Vlookup property of the worksheetfunction class

    Sub test()
        Dim inp As Workbook
        Set inp = Workbooks("input_dados.xlsm")
        For i = 2 To 3
            For x = 2 To 112
                Dim NewRange As Range
                Set NewRange = inp.Sheets("Flag_divide").Range(inp.Sheets("Flag_divide").Cells(3, 2), inp.Sheets("Flag_divide").Cells(112, 3))                   
                Dim var_macro As String
                var_macro = inp.Sheets("input").Cells(x + 1, 2).Value                  
                Dim marks As Integer
                marks = Application.WorksheetFunction.VLookup(var_macro, NewRange, 2, False)        
                If marks = 1 Then
                inp.Sheets("input").Cells(x + 1, i + 1).Value = (inp.Sheets("input").Cells(x + 1, i + 1).Value) / 100
                End If
            Next x
        Next i
    End Sub

Is there a reason why you cant do this with formulas?

Zac
– Zac

2019-07-02 14:06:42 +00:00
Commented Jul 2, 2019 at 14:06 — Zac
– Zac, Commented Jul 2, 2019 at 14:06

David Zemens · Accepted Answer · 2019-07-03 12:49:28Z

Common source of error here is that the searched value is not found, and this will throw an error (if you enter the formula on a worksheet, you'll see that result as well).

The preferred way of handling this is to use Application.VLookup rather than WorksheetFunction.VLookup. The former can return an error, the latter will not. This requires either changing your marks to a Variant type, or using an intermediary variable of Variant

Dim marks as Variant
marks = Application.VLookup(var_macro, NewRange, 2, False)
If IsError(marks) Then
    ' Do something, or do nothing...
Else
    If marks = 1 Then
        inp.Sheets("input").Cells(x + 1, i + 1).Value = (inp.Sheets("input").Cells(x + 1, i + 1).Value) / 100
    End If
End If

OR:

Dim marks as Integer ' or String, etc., but you're using Integer
Dim vlook as Variant
vlook = Application.VLookup(var_macro, NewRange, 2, False)
If IsError(vlook) Then
    marks = Empty
Else
    marks = vlook
End If
If marks = 1 Then
    inp.Sheets("input").Cells(x + 1, i + 1).Value = (inp.Sheets("input").Cells(x + 1, i + 1).Value) / 100
End If

Alternatively, you could just double-up on the function call, but I think this is inefficient (and ugly):

Dim marks as Integer ' or String, etc., but you're using Integer
If IsError(Application.VLookup(var_macro, NewRange, 2, False)) Then
    marks = Empty
Else
    marks = Application.VLookup(var_macro, NewRange, 2, False)
End If
If marks = 1 Then
    inp.Sheets("input").Cells(x + 1, i + 1).Value = (inp.Sheets("input").Cells(x + 1, i + 1).Value) / 100
End If

I'd suggest the first approach, for several reasons:

It seems a bit easier to read, you're not using additional variables that have limited or zero use elsewhere
On Error Resume Next is clunky, difficult to manage with multiple handlers in a single scope, often mis-used, etc.r
The Vlookup formula will return whatever is in the cell. This may not always be convertible to String or Integer etc.
Further, while you've declared marks As String, you're assigning it integer values. This is handled quietly by implicit type conversion, but that's (usually) best to avoid, if possible.

Great solution and broader than mine. I advise @usuario_estudo to mark this one as the final answer.

Roman Voronov · Accepted Answer · 2019-07-02 14:40:54Z

1

The most frequent situation, in this case, is that your formula didn't find any value. But it can't just assign an Error value to your variable since it's not a cell and it works differently for VBA. Here is how you can bypass this limitation:

On Error Resume Next ' Entering mode <compute it no matter what>
marks = Application.WorksheetFunction.VLookup(var_macro, NewRange, 2, False)
On Error GoTo 0 ' Returning back to a normal error handling
If IsEmpty(marks) Then marks = 0 ' Example on how to handle errors

answered Jul 2, 2019 at 14:40

Roman Voronov

2182 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to use vlookup in vba-excel?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related