setting Range from address

[Raj]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Norman Jones]

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
'<<==========

 

[Norman Jones]

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.

 

[Raj]

Thanks.It worked