Auto Update Cell (Row) References

[joecrabtree]

To all,

I have a macro that refers to a range eg:

Range.("P2100")

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 ("P250") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree

 

[Zone]

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

 

[joecrabtree]

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

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

To all,

I have a macro that refers to a range eg:

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 ("P250") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree- Hide quoted text -- Show quoted text -

 

[Zone]

Joe, there shouldn't be any problem with concatenating the range like
this:
myRg = "J" & CStr(Range("D3")) & ":J" & CStr(Range("D10"))

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

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

To all,

I have a macro that refers to a range eg:

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 ("P250") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree- Hide quoted text -- Show quoted text -