Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range using vlookup
Hi,
I want to set the range depending on a value derived by doing a vlookup in a range. The vlookup successfully retrieves the value and stores it in a string variable rsprangevalue. The retrieved value stored in rsprangevalue is: Worksheets("Parameters").Range("H3:K3") Next I want the range to be set using this value. The code used for this is Set rsprange = rsprangevalue (rsprange is declared as a Range variable) This fails with the "rsprangevalue" highlighted with the error message "Type Mismatch". Please help. Thanks in advance for the help. Regards, Raj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range using vlookup
On Jul 30, 10:35*pm, Raj wrote:
Hi, I want to set the range depending on a value derived by doing a vlookup in a range. The vlookup successfully retrieves the value and stores it in a string variable rsprangevalue. The retrieved value stored in rsprangevalue is: Worksheets("Parameters").Range("H3:K3") Next I want the range to be set using this value. The code used for this is Set rsprange = rsprangevalue (rsprange is declared as a Range variable) This fails with the "rsprangevalue" highlighted with the error message "Type Mismatch". Please help. Thanks in advance for the help. Regards, Raj I sent you an example I made that will do exactly what your looking for... Check your e-mail and let me know how it works out. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range using vlookup
Hi,
I have already worked along the lines you have done in your workbook. However, I need a solution for the problem that I am restating by means of code Sub test() 'Worksheets("Sheet3").Range("a1") contains the value: Worksheets("Parameters").Range("d1") Dim rsprange As Range Dim rsprangevalue As String rsprangevalue = Worksheets("Sheet3").Range("a1").Value 'Set rsprange = rsprangevalue 'Error message on above : rsprangevalue is highlighted and "Compile Error: Type Mismatch" is displayed Set rsprange = rsprangevalue.value 'Error message on above: rsprangevalue is highlighted and "Compile Error: Invalid Qualifier" is displayed End Sub Cell a1 of Sheet 3 contains a string : Worksheets("Parameters").Range("d1") I want to assign this string to the Range variable rsprange. I tried using the Set statement as above. I have noted the error messages below the set statements in the code as comments. Please help. Thanks and Regards, Raj On Jul 31, 7:49*am, wrote: On Jul 30, 10:35*pm, Raj wrote: Hi, I want to set the range depending on a value derived by doing a vlookup in a range. The vlookup successfully retrieves the value and stores it in a string variable rsprangevalue. The retrieved value stored in rsprangevalue is: Worksheets("Parameters").Range("H3:K3") Next I want the range to be set using this value. The code used for this is Set rsprange = rsprangevalue (rsprange is declared as a Range variable) This fails with the "rsprangevalue" highlighted with the error message "Type Mismatch". Please help. Thanks in advance for the help. Regards, Raj I sent you an example I made that will do exactly what your looking for... Check your e-mail and let me know how it works out.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range using vlookup
I am just an amateur, but I think you want to just put the worksheet name in
one cell and the range in a second sell. i.e. you can have the actual sheet name and range as variables, and then use the Set Range = Worksheet(variable1).Range(variable2) as a valid range. (or you can extract the variables using string functions.) Let's say you put "Parameters" in cell D1 on Sheet1 and ""H3:K3" in cell D2 on Sheet 1. If you let rpsrangvaluesheet = code for getting cell D1 on Sheet1 rpsrangevaluerange = code for getting cell D2 on Sheet1 Then your range set in Visual Basic is Set Range = Worksheets(rpsrangevaluesheet).Range(rpsrangevalue range) Hope this helps "Raj" wrote: Hi, I want to set the range depending on a value derived by doing a vlookup in a range. The vlookup successfully retrieves the value and stores it in a string variable rsprangevalue. The retrieved value stored in rsprangevalue is: Worksheets("Parameters").Range("H3:K3") Next I want the range to be set using this value. The code used for this is Set rsprange = rsprangevalue (rsprange is declared as a Range variable) This fails with the "rsprangevalue" highlighted with the error message "Type Mismatch". Please help. Thanks in advance for the help. Regards, Raj |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range using vlookup
This is exactly the pointer I was looking for. I have made the changes
and it is working. Thanks. Regards, Raj Trooper wrote: I am just an amateur, but I think you want to just put the worksheet name in one cell and the range in a second sell. i.e. you can have the actual sheet name and range as variables, and then use the Set Range = Worksheet(variable1).Range(variable2) as a valid range. (or you can extract the variables using string functions.) Let's say you put "Parameters" in cell D1 on Sheet1 and ""H3:K3" in cell D2 on Sheet 1. If you let rpsrangvaluesheet = code for getting cell D1 on Sheet1 rpsrangevaluerange = code for getting cell D2 on Sheet1 Then your range set in Visual Basic is Set Range = Worksheets(rpsrangevaluesheet).Range(rpsrangevalue range) Hope this helps "Raj" wrote: Hi, I want to set the range depending on a value derived by doing a vlookup in a range. The vlookup successfully retrieves the value and stores it in a string variable rsprangevalue. The retrieved value stored in rsprangevalue is: Worksheets("Parameters").Range("H3:K3") Next I want the range to be set using this value. The code used for this is Set rsprange = rsprangevalue (rsprange is declared as a Range variable) This fails with the "rsprangevalue" highlighted with the error message "Type Mismatch". Please help. Thanks in advance for the help. Regards, Raj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help to in setting up a proper Vlookup table and formula | Excel Worksheet Functions | |||
Macro for setting range of Vlookup formula's | Excel Programming | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
VLOOKUP in a dynamic setting | Excel Worksheet Functions | |||
setting range().hidden=True causes range error 1004 | Excel Programming |