How to loop a Vlookup macro for each value in a column

[billyheanue]

Hi Everyone,

So I have code that successfully vlookups ONE specific cell, in a column of "Lookup Values".

Private Sub CommandButton4_Click()


LookupVal = Sheets("Sheet1").Range("B16").Value
Range("A5").Formula = "=VLOOKUP(" & LookupVal & ",Sheet2!" & Sheets("Sheet2").Range("A4").CurrentRegion.Address & _
",Sheet1!G13,FALSE)"


End Sub

Im wondering how I would go about having the code also continue on the column, and lookup the next cell "B17", and then "B18", and so forth, until the values in the column become blank. I also want to emphasize that the code needs to keep the current region bit!

Is this a "For Each...Next" loop?

Thanks Everyone!!

 

[billyheanue]

Man - the forum is very busy today!

 

[RickXL]

Hi,

I am not sure that I know what you are doing exactly: A5, B16 and G13 sounds a bit confusing to me

Also, the CurrentRegion part can be done differently. Why don't you make the CurrentRegion on that worksheet a Table? You just need to go to the Insert tab and select Insert-->Table. It will then assign a name to the area. Assuming it is your first table it will be called Table1 - but you can rename it if you want.

Then your formula would look like this:

Private Sub CommandButton4_Click()

    Dim lr As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        lr = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("A5:A" & lr).Formula = "=VLOOKUP(B16,Table1,$G$13,FALSE)"
    End With

End Sub

I have made a few assumptions there:

1. Cell A5 is on Sheet1.
2. The end of Column B on Sheet1 will tell you how many rows need filling.
3. The look-up data has been changed into a table called Table1.
4. $G$13 does not need to change as it is copied down because it contains a constant column number.

 

[billyheanue]

Thanks Rick for your response. I'll try to make what I am trying to do clearer. On Sheet 1, I have a list of lookup values - approximately 8 numbers I need to do vlookups on. The lookup table is on sheet two. It needs to be a current region reference (current region of A4, sheet 2), because I need to perform this macro many times, and the lookup table dimensions are not constant.

I am trying to lookup the values on sheet one (beginning with the value in cell B16), and then print the vlookup results on sheet one as well, with the first result being printed in cell A5. So...

Cell B17 (sheet1) would print its vlookup result on A6
B18 would print its result on A7...

and so forth until the values in the list are all covered.