Hi,

Cell a1 of Sheet3 has the value : Worksheets("Parameters").Range("d4")

I want to assing this value to a range variable named rsprange.

I am using the statement:
Set rsprange = ThisWorkbook.Worksheets("Sheet3").Range("a1").Value

I am getting error message 424: Object Required.

Please help. I have the feeling this is something elementary that I am
not able to trouble-shoot.

Thanks in advance

Regards,
Raj

PS: I had posted this earlier today under the subject : Setting range
using vlookup. I am restating the problem after stripping extraneous
matter.

You're going to have to parse the contents of that cell to extract the worksheet
name and the address.

After you do that, you can use:

dim wksName as string
dim RngAddr as string
....some routine to parse those strings
set rsprange = thisworkbook.worksheets(wksname).range(rngaddr)

===
If I were you, I'd use two cells--one for the worksheet name and one for the
address of the cell.

with thisworkbook.worksheets("sheet3")
set rsprange = thisworkbook.worksheets(.range("b1").value) _
.range(.range("C1").value)
end with

Depending on what the name of that sheet and the address, it could make life
lots easier.


Raj wrote:
>
> Hi,
>
> Cell a1 of Sheet3 has the value : Worksheets("Parameters").Range("d4")
>
> I want to assing this value to a range variable named rsprange.
>
> I am using the statement:
> Set rsprange = ThisWorkbook.Worksheets("Sheet3").Range("a1").Value
>
> I am getting error message 424: Object Required.
>
> Please help. I have the feeling this is something elementary that I am
> not able to trouble-shoot.
>
> Thanks in advance
>
> Regards,
> Raj
>
> PS: I had posted this earlier today under the subject : Setting range
> using vlookup. I am restating the problem after stripping extraneous
> matter.

--

Dave Peterson

Dave,

Exactly the pointers I was looking for.

I have adopted the "If I were you" approach.

Thanks, once again.

Regards,
Raj

Dave Peterson wrote:

> You're going to have to parse the contents of that cell to extract the worksheet
> name and the address.
>
> After you do that, you can use:
>
> dim wksName as string
> dim RngAddr as string
> ...some routine to parse those strings
> set rsprange = thisworkbook.worksheets(wksname).range(rngaddr)
>
> ===
> If I were you, I'd use two cells--one for the worksheet name and one for the
> address of the cell.
>
> with thisworkbook.worksheets("sheet3")
> set rsprange = thisworkbook.worksheets(.range("b1").value) _
> .range(.range("C1").value)
> end with
>
> Depending on what the name of that sheet and the address, it could make life
> lots easier.
>
>
> Raj wrote:
> >
> > Hi,
> >
> > Cell a1 of Sheet3 has the value : Worksheets("Parameters").Range("d4")
> >
> > I want to assing this value to a range variable named rsprange.
> >
> > I am using the statement:
> > Set rsprange = ThisWorkbook.Worksheets("Sheet3").Range("a1").Value
> >
> > I am getting error message 424: Object Required.
> >
> > Please help. I have the feeling this is something elementary that I am
> > not able to trouble-shoot.
> >
> > Thanks in advance
> >
> > Regards,
> > Raj
> >
> > PS: I had posted this earlier today under the subject : Setting range
> > using vlookup. I am restating the problem after stripping extraneous
> > matter.
>
> --
>
> Dave Peterson