CODEWITHSUNDEEP
excelvba
usuario_estudo
usuario_estudo

Reputation: 11

How to use vlookup in vba-excel?

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

Upvotes: 0

Views: 852

Answers (2)

David Zemens
David Zemens

Reputation: 53623

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.

Upvotes: 3

Roman Voronov
Roman Voronov

Reputation: 208

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

Upvotes: 1

Related Questions