Columns as variable in Vlookup

[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

 

[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

Woops, a typo.

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

Should be... lcol_1 and lcol.

H

 

[Claus Busch]

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 >

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

 

[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

Thanks as always, Claus. Very nice!

Regards,
Howard

 

[CellShocked]

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

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.

 

[Howard]

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.

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