Help with using VLookup in a Loop using VBA

[JohnGow383]

Hi, I need help with the following.
I have a table in Sheet1 and a data table in Sheet2.
I want the 2nd column C of Table 1 to populate from the value of the adjacent cell in column B by looking up the master table in Sheet2, column T to read value in column U
I realize this can easily be done using the Vlookup formula but would prefer a VBA solution.
I will then have the macro fired from a worksheet change event using the range B2:B22 in sheet 2. So any change in that column will re-calculate Column C.
Here is the code I have but it doesn't work so any help will be appreciated.

Sub PlayLookUp()

Dim PlayGest As String
Dim PlayList As Range
Dim LoopList, Pokemon As Range
Dim lrT As Long

Set ws1 = Sheet1
Set ws2 = Sheet2

Set LoopList = ws1.Range("B2:B22")

lrT = ws2.Cells(Rows.Count, "T").End(xlUp).Row
Set PlayList = ws2.Range(ws2.Cells(4, "T"), ws2.Cells(lrT, "U"))

For Each Pokemon In LoopList
    PlayGest = Application.WorksheetFunction.VLookup(Pokemon.Value, PlayList, 2, False)
      Pokemon.Offset(0, 1).Value = PlayGest
 Next Pokemon
End Sub

 

[DanteAmor]

Thanks for posting on the forum.

I prefer to use the Find method:

Sub PlayLookUp()
  Dim c As Range, f As Range
  For Each c In Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B" & Rows.Count).End(3))
    Set f = Sheets("Sheet2").Range("T:T").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      c.Offset(, 1).Value = f.Offset(, 1).Value
    End If
  Next
End Sub

Without loop:

Sub PlayLookUp2()
  With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Range("B" & Rows.Count).End(3).Row)
    .Formula = "=IFERROR(vlookup(B2,Sheet2!T:U,2,0),"""")"
    .Value = .Value
  End With
End Sub

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

----- --

 

[JohnGow383]

Hi,

Thanks for the reply. When I reied that I was getting Error 9, subscript out of range in the line

[QUOTE]
Set f = Sheets("Sheet2").Range("T:T").Find(c.Value, , xlValues, xlWhole, , , False)
[/QUOTE]

There is nothing below the table in Sheet2, however there are populated cells in Sheet 1 below Range("B2:B22")

Thanks for posting on the forum.

I prefer to use the Find method:

Sub PlayLookUp()
  Dim c As Range, f As Range
  For Each c In Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B" & Rows.Count).End(3))
    Set f = Sheets("Sheet2").Range("T:T").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      c.Offset(, 1).Value = f.Offset(, 1).Value
    End If
  Next
End Sub

Without loop:

Sub PlayLookUp2()
  With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Range("B" & Rows.Count).End(3).Row)
    .Formula = "=IFERROR(vlookup(B2,Sheet2!T:U,2,0),"""")"
    .Value = .Value
  End With
End Sub

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

----- --

 

[JohnGow383]

Thanks for posting on the forum.

I prefer to use the Find method:

Sub PlayLookUp()
  Dim c As Range, f As Range
  For Each c In Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B" & Rows.Count).End(3))
    Set f = Sheets("Sheet2").Range("T:T").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      c.Offset(, 1).Value = f.Offset(, 1).Value
    End If
  Next
End Sub

Without loop:

Sub PlayLookUp2()
  With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Range("B" & Rows.Count).End(3).Row)
    .Formula = "=IFERROR(vlookup(B2,Sheet2!T:U,2,0),"""")"
    .Value = .Value
  End With
End Sub

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

----- --

Sorry, my mistake, it works perfectly. I am not used to using the actual sheet name and didn't change the sheet name. I usually use the code name. When I ran it it worked fine. Thanks for this, very much appreciated.