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

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

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

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

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