setting Range from address
 

Hi,

Depending on the value of Range("a1"), a range address is stored in
Range ("b1") as follows: Master!$B$31:$B$52. How can I use the value
in Range("b1") to set a range in vba code?

Any other suggestions also welcome.

Thanks in advance for the help.

Regards,
Raj

setting Range from address
 

Something like this...

Set MyRange = Range(Range("B1").Value)

or this...

Set MyRange = Range(Cells(1, "B").Value)

(depending on how you need to reference the range) should work.

Rick


"Raj" wrote in message
...
Hi,

Depending on the value of Range("a1"), a range address is stored in
Range ("b1") as follows: Master!$B$31:$B$52. How can I use the value
in Range("b1") to set a range in vba code?

Any other suggestions also welcome.

Thanks in advance for the help.

Regards,
Raj

setting Range from address
 

Hi Raj,

Try something like:

'==========
Public Sub TestIt()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr As String
Dim arr As Variant
Dim rng As Range

Set WB = ThisWorkbook '<<==== CHANGE
Set SH = WB.Sheets("DataSheet") '<<==== CHANGE

sStr = ActiveSheet.Range("B1").Value
arr = Split(sStr, "!")

Set rng = WB.Sheets(arr(0)).Range(arr(1))
MsgBox rng.Address(External:=True)
End Sub
'<<==========



---
Regards.
Norman


"Raj" wrote in message
...
Hi,

Depending on the value of Range("a1"), a range address is stored in
Range ("b1") as follows: Master!$B$31:$B$52. How can I use the value
in Range("b1") to set a range in vba code?

Any other suggestions also welcome.

Thanks in advance for the help.

Regards,
Raj

setting Range from address
 

Hi Raj,

As Rick demonstrates, it is not necessary
to split the text in the cell.

Therefore, go with Rick's better and simpler
sugestion.



---
Regards.
Norman

setting Range from address
 

Thanks.It worked



On May 26, 9:58*pm, "Norman Jones"
wrote:
Hi Raj,

As Rick demonstrates, it is not necessary
to split the text in the cell.

Therefore, go with Rick's better and simpler
sugestion.

---
Regards.
Norman