Vlookup Loop

[Sundae]

Hi All,

I have the following code that works fine on returning the value for cell B20, but that is not what I need:

Sub Test_Lookup()
Dim rngRequested As Range, rng As Range
Set rngRequested = Sheets("Role").Range("C23")
Set rng = Workbooks("Data.xlsx").Worksheets("Earl").Range("Wager")
rngRequested.Value = ""
Dim res As Variant
Dim errNum As Integer

On Error Resume Next
res = Application.WorksheetFunction.VLookup(Sheets("Role").Range("B20"), rng, 2, 0)

errNum = Err.Number
If errNum <> 0 Then
res = "Error: " & errNum
End If
rngRequested.Value = res
End Sub
************

What I would like to happen is if Column A = "DRAKE" then find ("B & x" &"*" * Range (DPT) in another workbook DATA, Range Wager.

The reason I have to do the find DRAKE is because the lookup field is every 5th row.

Also, this will be a loop within a loop, as I have additional code that will copy and paste this range a specified # of times.

Thanks for any help!