CODEWITHSUNDEEP
vbavlookup
Roy Abbas
Roy Abbas

Reputation: 11

VBA Vlookup mismatch

I have cells to look for in sheet4 and the lookup table is in sheet2 Range("A16:B25"). When I run this code it is giving me a mismatch error. Why is it doing this?

Dim i As Integer
Dim lrow As Long
Dim x As Integer

Sheet4.Activate
lrow = Sheet4.Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lrow

    Cells(i, 1).Activate
    x = Application.VLookup(ActiveCell.Offset(0, 0).Value, Worksheets(2).Range("A16:B25"), 2, False)

    If x <> Cells(i, 2).Value Then
        Cells(i, 2).Interior.Color = RGB(255, 0, 0)
    Else
    End If

Next i

Upvotes: 1

Views: 36

Answers (1)

mooseman
mooseman

Reputation: 2017

1: You do not need to activate anything unless it is absolutely necessary. Use explicit references (workbook.worksheet.cell or .range) 2: You need to check if your vlookup is returning an error. 3: ActiveCell.Offset(0, 0).Value is just the activecell since you have no offset.

Try to get this into your code, you may have to adjust things for your specific use.

sub lookup_color()
Dim i As Integer
Dim lrow As Long
Dim vReturnVal As Variant

lrow = Sheet4.Range("A" & Rows.count).End(xlUp).Row
For i = 2 To lrow
   vReturnVal = Application.VLookup(Sheet4.Cells(i, 1).Value, Worksheets(2).Range("A16:B25"), 2, False)
     If Not IsError(vReturnVal) Then
        If vReturnVa <> Sheet4.Cells(i, 2).Value Then
        Sheet4.Cells(i, 2).Interior.Color = RGB(255, 0, 0)
       End If
    End iF
Next i
End sub

Upvotes: 2

Related Questions