Excel Macro - Set Range to Text in Cell

[roxy26girl]

Hi,
I'm relatively new to VBA, so excuse me if this is a dumb question. I have a workbook with a tab that users enter different variables into. One cell is meant to input a range that later on will be used to perform selections on other tabs. I can't seem to set my range to the text in the input cell. An abbreviated form of the macro I'm trying to use is this:

Sub Macro1()
Dim RetrieveRange As range

Set RetrieveRange = range(Worksheets("Admin").range("B1").Text)

MsgBox RetrieveRange.Address

End Sub

I keep getting this error: "Run-time error '1004': Method 'Range' of object '_Global' failed"

Can anyone help?

Thanks!

 

[jeffreybrown]

How about...

Sub Macro1()
    Dim RetrieveRange As Range
    Set RetrieveRange = Worksheets("Admin").Range("B1")
    MsgBox RetrieveRange.Address
End Sub

 

[*shudder*]

Something like this:

Sub Macro1()
Dim RetrieveRange As Variant

Set RetrieveRange = Worksheets("Admin").Range("B1")

'Cell Address
MsgBox RetrieveRange.Address
'Cell Text
MsgBox RetrieveRange.Text

End Sub

Although from your description you want to take the text string from the cell and then select the range it refers to? If so this doesn't really get you where you want to be.

 

[*shudder*]

You will need something more like this:

Sub Macro2()

Dim RetrieveRange As Range

Set RetrieveRange = Worksheets("Admin").Range(Worksheets("Admin").Range("B1").Value)

RetrieveRange.Select

End Sub

 

[Glory]

What's the text in the target cell?