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

 

[StephenCrump]

It's not totally clear what you're trying to do here.

But to get the conversation started, let's say Range("A37").CurrentRegion is A37:C47. Then this code:

LookupValue = 123  'say
Range("A1").Formula = "=VLOOKUP(" & LookupValue & "," & Range("A37").CurrentRegion.Address & ",Sheet2!K99,FALSE)"

will put this formula into cell A1 of the ActiveSheet:

=VLOOKUP(123,$A$37:$C$47,Sheet2!K99,FALSE)

Am I on the right track?

 

[billyheanue]

Hi Stephen, thanks for getting back to me.

Your solution addresses the current region problem nicely, however when I try

Private Sub CommandButton1_Click()
LookupValue = Cells("C22").Value ' More specific referencing? The lookup value C22 is on Sheet 2, just like the col index value.
Range("A1").Formula = "VLOOKUP(" & LookupValue & "," Range("A37").CurrentRegion.Address & ",Sheet3!I7,FALSE)" 'Any way to be more specific about the current region of A37? Its on sheet one.

End Sub

I get a syntax error. How would you write this without having to rely on an active sheet for referencing?

 

[StephenCrump]

You can qualify any necessary sheet names like this:

LookupValue = Sheets("Sheet2").Range("C22").Value
Range("A1").Formula = "=VLOOKUP(" & LookupValue & "," & Sheets("Sheet1").Range("A37").CurrentRegion.Address & ",Sheet3!I7,FALSE)"

Any unqualified references, e.g. Range("A1"), will refer to the ActiveSheet.

 

[StephenCrump]

Oops, careless with my qualifiers:

Range("A1").Formula = "=VLOOKUP(" & LookupValue & ",Sheet1!" & Sheets("Sheet1").Range("A37").CurrentRegion.Address & ",Sheet3!I7,FALSE)"

 

[billyheanue]

Hey Again Stephen,

So I have simplified the whole sheet referencing thing, and now I am only working with 2 sheets, Sheet 1 and Sheet 2.

This code works without encountering an error:

Private Sub CommandButton4_Click()

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


End Sub

My lookup values are on sheet 1, the Current Region table is on Sheet 2(current region of A4 on sheet 2), and the col_index_value is on Sheet 1.

However: I get a circular reference warning, and the code returns a value of "0" in the correct cell of A5 on Sheet 1. I think it never sees the table on sheet 2 to do the lookup!

Should I try using a dim and storing the Current Region (a4) on Sheet 2 as a variable, and utilize that?

Thanks!

 

[billyheanue]

Hi Stephen, I have simplified the sheet referencing, and now only have two sheets. I'm getting a circular reference warning.

Private Sub CommandButton4_Click()

LookupVal = Sheets("Sheet1").Range("B16").Value
Range("A5").Formula = "=VLOOKUP(" & LookupValue & "," & Sheets("Sheet2").Range("A4").CurrentRegion.Address & _
",Sheet1!G13,FALSE)"   ' the new column index value is found on sheet 1 cell g13. Code not seeing the current region?


End Sub

The current region table is on sheet 2, and the result cell (a5) is on sheet 1. The result cell returns a value of "0" (incorrect), and prompts a circular reference warning.
Should I try using

dim tabl = Sheets("Sheet2").Range("A4").CurrentRegion

and using tabl in the vlookup formula instead?

THanks!

 

[StephenCrump]

LookupValue is 0 because you have set a value for LookupVal, not LookupValue.

I recommend you use Option Explicit, which will force you to declare all variables and avoid problems like this.

Try:

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

I am assuming LookupVal is numeric?

 

[billyheanue]

Great! That works perfect. LookupVal, when it is finally implemented, will be a string of characters - a manufacturer's ID tag, that could include letters or numbers.

For the above code, how would you go about automatically moving on the vlookup to the next LookupVal on the list?

Thanks!

 

[StephenCrump]

Sorry for the delay ...

If your lookup_values are in Sheet1!B16:B26, say, and you want VLOOKUP formulae in A5:A15 of the ActiveSheet, say, to use these values, then you could use something like:

Range("A5:A15").Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A4").CurrentRegion.Address & ",Sheet1!G$13,FALSE)"

'OR, if you determine the number of lookup_value rows, lRows, dynamically:
Range("A5").Resize(lRows).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A4").CurrentRegion.Address & ",Sheet1!G$13,FALSE)"