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

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.

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 -

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

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