Looping through a range of cells using xlookup

[Bobstar]

Hi all

From source data, I have two columns, client id and names.

I have a second tab with a range of clients ids and for each client id in that range I would like vba to loop through and apply xlookup to get the name from the source data.

Any help is much appreciated.

 

[offthelip]

The really fast way to do this is using the VBA dictionary , Try this code :
I have assume the on sheet1 you have cclient Id in coluimn A and Name in column B
on sheet 2 I assumed you have clinent ID on column A

Sub dictionarylookup()
' this shows the use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   Lastcol = 2
   Set Dic = CreateObject("Scripting.dictionary")
   With ActiveWorkbook.Sheets("sheet1")
   LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
   Ary = .Range(.Cells(1, 1), .Cells(LastRow, Lastcol))
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 2)
   Next i
   With ActiveWorkbook.Sheets("Sheet2")
   LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
   ary2 = .Range(.Cells(1, 1), .Cells(LastRow2, 1))
      For i = 1 To UBound(ary2)
         .Range(.Cells(i, 2), .Cells(i, 2)).Value = Dic(ary2(i, 1)) 'this matches the value given by the index ary2 in the dictionary
      Next i
   End With
End Sub

 

[Bobstar]

The really fast way to do this is using the VBA dictionary , Try this code :
I have assume the on sheet1 you have cclient Id in coluimn A and Name in column B
on sheet 2 I assumed you have clinent ID on column A

Sub dictionarylookup()
' this shows the use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   Lastcol = 2
   Set Dic = CreateObject("Scripting.dictionary")
   With ActiveWorkbook.Sheets("sheet1")
   LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
   Ary = .Range(.Cells(1, 1), .Cells(LastRow, Lastcol))
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 2)
   Next i
   With ActiveWorkbook.Sheets("Sheet2")
   LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
   ary2 = .Range(.Cells(1, 1), .Cells(LastRow2, 1))
      For i = 1 To UBound(ary2)
         .Range(.Cells(i, 2), .Cells(i, 2)).Value = Dic(ary2(i, 1)) 'this matches the value given by the index ary2 in the dictionary
      Next i
   End With
End Sub

Thanks. This works very well

 

[Peter_SSs]

Would this also work for you?
I have assumed that on Sheet2 the IDs start in cell A2.

Sub Get_Name()
  With Sheets("Sheet2").Range("B2:B" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B,"""")"
    .Value = .Value
  End With
End Sub

 

[Peter_SSs]

Thanks. This works very well

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.