Columns as variable in Vlookup

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

How do I translate the Long returned by loc_1 and lcol into something the vlookup formula will accept? On my test sheet they are 4 & 5 with current data.

Thanks,
Howard

Option Explicit

Sub Vx_x()

Dim lcol As Long
Dim lcol_1 As Long

lcol_1 = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 1
lcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

MsgBox lcol_1 & " " & lcol

Range("G12").Formula = "=vlookup(F12,lcol_1:lcol,2,0)"

End Sub
 
How do I translate the Long returned by loc_1 and lcol into something the vlookup formula will accept? On my test sheet they are 4 & 5 with current data.



Thanks,

Howard



Option Explicit



Sub Vx_x()



Dim lcol As Long

Dim lcol_1 As Long



lcol_1 = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 1

lcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column



MsgBox lcol_1 & " " & lcol



Range("G12").Formula = "=vlookup(F12,lcol_1:lcol,2,0)"



End Sub
Click to expand...

Woops, a typo.

<How do I translate the Long returned by loc_1 and lcol >

Should be... lcol_1 and lcol.

H
 
Hi Howard,

Am Fri, 21 Jun 2013 17:04:28 -0700 (PDT) schrieb Howard:
<How do I translate the Long returned by loc_1 and lcol >
Click to expand...

try:
Sub Vx_x()

Dim lcol As Long
Dim lcol_1 As Long
Dim LRow As Long

With ActiveSheet
lcol_1 = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Range("G12").Formula = "=vlookup(F12," & _
Range(Cells(1, lcol_1), Cells(LRow, lcol)).Address & ",2,0)"
End With
End Sub

Regards
Claus Busch
 
Hi Howard,



Am Fri, 21 Jun 2013 17:04:28 -0700 (PDT) schrieb Howard:






try:

Sub Vx_x()



Dim lcol As Long

Dim lcol_1 As Long

Dim LRow As Long



With ActiveSheet

lcol_1 = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1

lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row



.Range("G12").Formula = "=vlookup(F12," & _

Range(Cells(1, lcol_1), Cells(LRow, lcol)).Address & ",2,0)"

End With

End Sub



Regards
Click to expand...

Thanks as always, Claus. Very nice!

Regards,
Howard
 
Hi Howard,

Am Fri, 21 Jun 2013 17:04:28 -0700 (PDT) schrieb Howard:


try:
Sub Vx_x()

Dim lcol As Long
Dim lcol_1 As Long
Dim LRow As Long

With ActiveSheet
lcol_1 = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
lcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Range("G12").Formula = "=vlookup(F12," & _
Range(Cells(1, lcol_1), Cells(LRow, lcol)).Address & ",2,0)"
End With
End Sub

Regards
Claus Busch
Click to expand...


Assign a cell a range name and place the column number in that cell,
and make the vlookup statement refer to that rangename to gather the
column number. Then, you can use custom dropdown lists to create dynamic
vlookups.
 
Assign a cell a range name and place the column number in that cell,

and make the vlookup statement refer to that rangename to gather the

column number. Then, you can use custom dropdown lists to create dynamic

vlookups.
Click to expand...

I think I see where you are going with this. I'll toil with it. Hard to beat Claus' solution though.

Thanks, CellShocked.

Regards,
Howard
 
Back
Top