Writing Vlookup w/ dynamic 'table_array' in syntax

[aguice]

The syntax for Vlookup is (lookup_value,table_array,col_index_num,[range_lookup]) and I am trying to write a vlookup formula with VBA.

The important code leading up to the vlookup is this:
Dim POS As Range
<make selection of range>
Set POS = Selection

Right now it looks like this:
Cells(2,8) = "=VLOOKUP(D2," & Range(POS) & ",52,FALSE)"


Note: the 52 will also be a variable such as "colVar" later on, but I am currently crossing one bridge, and will get to that one when I can.

Appreciate any help anyone can give me!

[Artik]

[VBA] Dim POS As Range

If TypeName(Selection) = "Range" Then
Set POS = Selection

Cells(2, 8).Formula = "=VLOOKUP(D2," & POS.Address & ",52,FALSE)"

Set POS = Nothing
End If
[/VBA]

Artik

[aguice]

Artik -

Thank You very much! All I needed was that '.Address' part of working with the range. Works like a charm!

[aguice]

Artik, I have tried to find a button that gives you credit for helping me out, and actually shows your post as the answer, and have yet to find one. If there is one, and I am just blind - please let me know, and I will gladly give you the credit you deserve.

Thanks!

[arkusM]

Glad you find an answer, though I wonder if you won the battle but are losing the war.
Basing your vlook on a selection and pulling a value up 52 columns over could be very problematic. I can understand the desire have dynamic ranges what is ensureing that the first row of the selection is in the correct column and encompasses enough columns to return the desired field (ie 52 in the above example)?
Maybe you have no other feasable options for setting a range?
Just some thoughts.

[aguice]

Appreciate the thoughts, but the first 5 columns are set, and then the other columns are dependent on how many weeks are put into a inputbox that comes up earlier. With those 2 factors, I should have things under control.

[Artik]

If there is one, and I am just blind - please let me know, and I will gladly give you the credit you deserve.

Visit www.excelforum.pl, ask a question in Polish (only!). How I can help you, press button "Pomógł".

Artik