VBA Vlookup for multiple cells or Range

Question

I don't have much knoledge of VBA. And also weak in English.

The below code is for VLOOKUP result in same cell, which is working well, but now I also need VLOOKUP values for Range("B1:B10") (nearby column ).

My VLOOKUP Table Is: - ThisWorkbook.Sheets("ItemName").Range("D2:G10001")

Column Index Number: 3

Result I Need: If i type sumthing in any cell in Range("A1:A10"), and if the value found in VLOOKUP Table, then The Third Column's Value from the VLOOKUP Table must be show in nearby cell in Range("B1:B10")

For Example: If i type something in Range A3 the vlookup result must be show in Range B3.

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCell As Range, m, v
    Dim rngCell1 As Range, m1, v1
Check1:
    If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then GoTo Check2:
    
    For Each rngCell In Range("A1:A10")
        v = rngCell.Value
        If Len(v) > 0 Then

            'See if the value is in your lookup table
            m = Application.VLookup(v, _
                 ThisWorkbook.Sheets("ItemName").Range("D2:G10001"), 2, False)

            'If found a match then replace wiht the vlookup result
            If Not IsError(m) Then rngCell.Value = m
End If
    Next
Exit Sub

Check2:
End Sub

What exactly are you having trouble with? Looks like you have the code knowledge? — SJR
No Sir, I don't know coding, the above code also get from this site. Please help me. — Prabhat Vishwas

SJR SJR · Accepted Answer · 2020-10-01T19:14:58

Think this does what you want. I also restructured the code to avoid the Gotos.

I also disabled events to avoid an infinite loop (not sure how you avoided that yourself).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range, m1 As Variant, m2 As Variant

If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub

For Each rngCell In Intersect(Target, Range("A1:A10"))
    If Len(rngCell.Value) > 0 Then
        m1 = Application.VLookup(rngCell.Value, Range("D2:G10001"), 2, False)
        m2 = Application.VLookup(rngCell.Value, Range("D2:G10001"), 3, False)
        If Not IsError(m1) Then
            Application.EnableEvents = False
            rngCell.Value = m1
            rngCell.Offset(, 1).Value = m2
            Application.EnableEvents = True
        End If
    End If
Next

End Sub

VBA Vlookup for multiple cells or Range

1 Answers