Vlookups in VBA using a current region

[billyheanue]

Hi all,

I am looking for a general theory as to go about using Vlookups with "CurrentRegion" arrays. This is because the lookup table is never strictly defined. That is,

1) The actual lookup table will be the current region of .. say "A37".

2) Have the col_index_number value refer to a literal value IN A SEPARATE CELL (e.g "K99")that is outside the table, and possibly even on a different sheet.

3) And finally, have the lookup value paste in the newly created/formatted column as given by:

Private Sub CommandButton3_Click()Set Rng = Range("A37").CurrentRegion
Rng.Select
Selection.Copy
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.resize(numRows + 0, numColumns + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


End Sub

I think number 2 is giving me the most trouble. Would I just use

dim colValue = cells("K99")

...and then use the variable colValue instead of putting in an actual number when doing the formula in VBA?


Thanks Everyone

 

[billyheanue]

Hi Stephen,

When you determining the number of rows dynamically, what do you mean? Does that latter part require storing "lrows" as a variable? I get an error when trying to run it.

Thanks!

 

[StephenCrump]

Yes, lRows is a VBA variable.

Let's say you have lookup_values in a single column starting in Sheet1!B16.

Then depending on the way Sheet1 is organised, alternatives for determining lRows include:

Dim lRows As Long
Const START_ROW = 16

'If you know column B is empty below the lookup_values
lRows = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row - START_ROW + 1

'OR
'If you know there is at least one blank row between the lookup_values and further info in column B
With Sheets("Sheet1").Range("B" & START_ROW)
    If .Offset(1).Value = "" Then
        lRows = 1
    Else
        lRows = .End(xlDown).Row - START_ROW + 1
    End If
End With

'OR
'If your lookup_values are in a named range
lRows = Range("YourRangeName").Rows.Count