Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and named ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and named ranges
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and named ranges
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do Vlookup calculate quicker using named ranges?! | Excel Worksheet Functions | |||
Formula To Use A Dynamic Named Ranges With VLOOKUP On Different Workbooks | Excel Worksheet Functions | |||
can VLOOKUP use dynamic named ranges? | Excel Discussion (Misc queries) | |||
vlookup and named ranges | Excel Worksheet Functions | |||
vlookup and named ranges | Excel Worksheet Functions |