Vlookup and named ranges

[XCubed]

Hi. I have a probelm with a particular part of some code that, despite all my
efforts, will not run! This snippet of code is designed to define a range
based on named columns . i.e. a range of columns, that will be passed onto a
vlookup command

Dim lookup_range As Range
With Worksheets("MyWorksheet")
start_row = .Range("start_row_column").Column
end_row = .Range("end_row_colummn").Column

Set lookup_range = .Range(.Columns(start_row), .Columns(end_row)).Address
(ReferenceStyle:=xlA1, ColumnAbsolute:=False)

End With

If lookup_range is defined as a variant it will return something similar to
"$A:$D", which can then be incorporated into a vlookup, however if it is
defined as a range then it fails to assign.

I have also tired writing the code so that it will return a vlookup fucntion
with exactly the same variables as if I were to simply add it to the
worksheet, and I have also used used code where I explicitly define the range
and not use a variable and it all works!

My probelm lies in:
1. Sucessffully asigning lookup_range as a Range
2. then getting the vlookup to accept it!

Any ideas or suggestions appreciated!

Thanks

 

[Patrick Molloy]

This code
start_row = .Range("start_row_column").Column
seems to be assigning a column number to a variable

adding
OPTION EXPLICIT
to the start of your code module will help you define your variables.

you cannot SET a range object to an address, only to a range object.
so maybe
Set lookup_range = .Range(.Columns(start_row), .Columns(end_row)).Address
should be
Set lookup_range = .Range( start_row & ":" & end_row)

thus if start_r0w = 10 and end_row = 20
look_up range will be set to ROWS 10:20
if its columns you need
Set lookup_range = .Range( .Cells(1,start_row),.Cells(200, end_row))

you aren't really clear about what your parameters are I'm afraid.

Now, lets say that you have a table on a sheet range named "MyData"

to use VLOOKUP on this in code:

DIM Source as Range
DIM Result as String
DIM FindWhat as String ' item to look up
DIM WhichColumn as Long ' column for return value

SET Source = Range("MyData")
WhichColumn = 3
FindWhat = "Hello World"
Result = WorksheetFunction.VLookup(FndWhat,Source,WhichColumn,False)


HTH

 

[Rowan]

Try:

Sub XCube()
Dim lookup_range As Range
Dim start_row As Integer
Dim end_row As Integer
With Worksheets("MyWorksheet")
start_row = .Range("start_row_column").Column
end_row = .Range("end_row_column").Column

Set lookup_range = .Range(.Columns(start_row), .Columns(end_row))
End With
ActiveCell.Formula = "=VLOOKUP(F1," & lookup_range.Address & ",4,0)"
End Sub

Regards
Rowan

 

[XCubed]

What ever I didi I managed to get it working. I removed the .Address.....
from the range definition and it accetped it as a range......oddly enought I
tried that before and it didn't appear to work. Probably a minor syntax error
i missed out on. After thant my vlookup worked correctly.

Thanks for your help!