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

On Friday, June 21, 2013 4:59:34 PM UTC-7, Howard wrote:
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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Friday, June 21, 2013 10:39:26 PM UTC-7, Claus Busch wrote:
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


Thanks as always, Claus. Very nice!

Regards,
Howard

On Sat, 22 Jun 2013 07:39:26 +0200, Claus Busch
wrote:

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


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.

On Sunday, June 23, 2013 2:05:08 PM UTC-7, CellShocked wrote:
On Sat, 22 Jun 2013 07:39:26 +0200, Claus Busch

wrote:



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





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