To all,

I have a macro that refers to a range eg:

Range.("P2:P100")

However I want to be able to change this range depending on what the
user enters in a cell ( A1). SO for example if the user enters '200'
the Range will be ("P2: P200"), or if the user enters 50 the range will
be ("P2:P50") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree

Joe, try this:
Dim myRg As String
myRg = "P2:P" & CStr([A1])
James

joecrabtree wrote:
> To all,
>
> I have a macro that refers to a range eg:
>
> Range.("P2:P100")
>
> However I want to be able to change this range depending on what the
> user enters in a cell ( A1). SO for example if the user enters '200'
> the Range will be ("P2: P200"), or if the user enters 50 the range will
> be ("P2:P50") etc.
>
> Any help on this would be much appreciated. Thanks
>
> Joseph Crabtree

Thanks for that. I have now got it to work.

using:

myRg = "J2:J" & CStr(Range("D10"))

Set ws = Sheets("FINAL Averages")

Set r = Range(myRg)



I have another question, is it possible to change this so that I can
define both starting and finishing cells? I tried adding another string
but it didn't work. Any ideas?

i.e if I enter 3 into D9, and 10 into D10 it will give the range
J3:J10?

Thanks for your help,

Regards

Joseph Crabtree




On Dec 6, 3:20 pm, "Zone" <jkend69...@aol.com> wrote:
> Joe, try this:
> Dim myRg As String
> myRg = "P2:P" & CStr([A1])
> James
>
>
>
> joecrabtree wrote:
> > To all,
>
> > I have a macro that refers to a range eg:
>
> > Range.("P2:P100")
>
> > However I want to be able to change this range depending on what the
> > user enters in a cell ( A1). SO for example if the user enters '200'
> > the Range will be ("P2: P200"), or if the user enters 50 the range will
> > be ("P2:P50") etc.
>
> > Any help on this would be much appreciated. Thanks
>
> > Joseph Crabtree- Hide quoted text -- Show quoted text -

Joe, there shouldn't be any problem with concatenating the range like
this:
myRg = "J" & CStr(Range("D3")) & ":J" & CStr(Range("D10"))
James
joecrabtree wrote:
> Thanks for that. I have now got it to work.
>
> using:
>
> myRg = "J2:J" & CStr(Range("D10"))
>
> Set ws = Sheets("FINAL Averages")
>
> Set r = Range(myRg)
>
>
>
> I have another question, is it possible to change this so that I can
> define both starting and finishing cells? I tried adding another string
> but it didn't work. Any ideas?
>
> i.e if I enter 3 into D9, and 10 into D10 it will give the range
> J3:J10?
>
> Thanks for your help,
>
> Regards
>
> Joseph Crabtree
>
>
>
>
> On Dec 6, 3:20 pm, "Zone" <jkend69...@aol.com> wrote:
> > Joe, try this:
> > Dim myRg As String
> > myRg = "P2:P" & CStr([A1])
> > James
> >
> >
> >
> > joecrabtree wrote:
> > > To all,
> >
> > > I have a macro that refers to a range eg:
> >
> > > Range.("P2:P100")
> >
> > > However I want to be able to change this range depending on what the
> > > user enters in a cell ( A1). SO for example if the user enters '200'
> > > the Range will be ("P2: P200"), or if the user enters 50 the range will
> > > be ("P2:P50") etc.
> >
> > > Any help on this would be much appreciated. Thanks
> >
> > > Joseph Crabtree- Hide quoted text -- Show quoted text -