Setting range using vlookup

[Raj]

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

 

[Internetdomainowner]

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.

 

[Raj]

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

 

[Trooper]

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(rpsrangevaluerange)

Hope this helps

 

[Raj]

This is exactly the pointer I was looking for. I have made the changes
and it is working.

Thanks.

Regards,
Raj