I have set "G" as the a range.
In the later part, I need to use "G" as a reference to set itself as another 
range.
what is the correct syntex?


Set G = Worksheets(E).Range("B21")
....
Set G = Range(G).End(xlRight)       <-----Error occurs here

Please help!

You are confusing the range with its name:

Set G = Worksheets(E).Range("B21")
Set G = G.End(xlRight)
-- 
Gary''s Student - gsnu201001


"Terry" wrote:

> I have set "G" as the a range.
> In the later part, I need to use "G" as a reference to set itself as another 
> range.
> what is the correct syntex?
> 
> 
> Set G = Worksheets(E).Range("B21")
> ...
> Set G = Range(G).End(xlRight)       <-----Error occurs here
> 
> Please help!

Hi Terry,

Also should be xlToright not just xlRight

Set g = Worksheets(E).Range("B21")

Set g = g.End(xlToRight)

I assume that E is set to a numeric value for the worksheet. If the name of 
the worksheet is E then the E should be in double quotes.
-- 


Regards,

OssieMac


"Gary''s Student" wrote:

> You are confusing the range with its name:
> 
> Set G = Worksheets(E).Range("B21")
> Set G = G.End(xlRight)
> -- 
> Gary''s Student - gsnu201001
> 
> 
> "Terry" wrote:
> 
> > I have set "G" as the a range.
> > In the later part, I need to use "G" as a reference to set itself as another 
> > range.
> > what is the correct syntex?
> > 
> > 
> > Set G = Worksheets(E).Range("B21")
> > ...
> > Set G = Range(G).End(xlRight)       <-----Error occurs here
> > 
> > Please help!

Hi OssieMac,

Thank you very much for the advise!
it works perfect!


"OssieMac" wrote:

> Hi Terry,
> 
> Also should be xlToright not just xlRight
> 
> Set g = Worksheets(E).Range("B21")
> 
> Set g = g.End(xlToRight)
> 
> I assume that E is set to a numeric value for the worksheet. If the name of 
> the worksheet is E then the E should be in double quotes.
> -- 
> 
> 
> Regards,
> 
> OssieMac
> 
> 
> "Gary''s Student" wrote:
> 
> > You are confusing the range with its name:
> > 
> > Set G = Worksheets(E).Range("B21")
> > Set G = G.End(xlRight)
> > -- 
> > Gary''s Student - gsnu201001
> > 
> > 
> > "Terry" wrote:
> > 
> > > I have set "G" as the a range.
> > > In the later part, I need to use "G" as a reference to set itself as another 
> > > range.
> > > what is the correct syntex?
> > > 
> > > 
> > > Set G = Worksheets(E).Range("B21")
> > > ...
> > > Set G = Range(G).End(xlRight)       <-----Error occurs here
> > > 
> > > Please help!