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

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