VlookUp in Vba

[Meghnath]

if i will change the 1st row values in the table(Sheet1),code wouldn't show #N/A.
Kindly tell me what's wrong in the code.

 

[NARAYANK991]

Hi ,

Try this :

Sub LookUp3()
    Dim lastrow As Long, i As Long
    Dim lookuprange As Range
   
    With Sheet1
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        Set lookuprange = .Range("A1:B" & lastrow)
        For i = 2 To lastrow
            .Cells(i, 3) = Application.VLookup(Sheet2.Cells(i, 1), lookuprange, 2, False)
        Next i
    End With
End Sub

Narayan

 

[Hui]

To place the looked up values into Sheet2 which is what I think you want
the code will be

Sub LookUp3()
  Dim lastrow As Long, i As Long
  Dim lookuprange As Range
  
  lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
  Set lookuprange = Sheet1.Range("A1:B" & lastrow)
  With Sheet2
  For i = 2 To lastrow
  .Cells(i, 2) = Application.VLookup(Sheet1.Cells(i, 1), lookuprange, 2, False)
  Next i
  End With
End Sub

 

[Lori]

Perhaps worth adding that this kind of operation could also be done in one "single hit":

Sheet2.[B2:B8]=Application.Vlookup(Sheet2.[A2:B8],Sheet1.[A2:B8],2,0)

To make this dynamic either replace the references with ranges similar to method above or using Names/Table references.