Vlookup and named ranges
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,WhichColu mn,False)
HTH
"XCubed" wrote:
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
|