Passing a string as a range

[Pesky_Weasel]

I would like to pass the contents of a cell, which contains a reference to a range (ie. The contents of the cell: 'Staff Data Oct 2002'!R1C1:R2500C32 ) to a VBA sub as a range.

In particular this is for a pivot table data source.

I have tried Dim(ing) it as Range, Variant, Text, String etc but it keeps falling over.

Any suggestions would be most welcome.

Regards
Pesky_Weasel

 

[Colo]

Hi Pesky,
Assume The contents of the cell is located A1, please try like as follows.
You can use Application.ConvertFormula.

<pre>
Sub Test()
Dim rngYours As Range
Set rngYours = ChangeToRange([A1])
MsgBox rngYours.Address(0, 0)
End Sub

Function ChangeToRange(ByVal rng As Range) As Range
Dim str As String, i As Integer, j As Integer, k As String
str = rng.Value
i = InStr(1, str, "!")
j = InStr(1, str, ")")
If i = 0 Or j = 0 Then Exit Function
k = Mid(str, i + 1, j - i - 1)
Set ChangeToRange = _
Range(Application.ConvertFormula(k, xlR1C1, xlA1, xlAbsolute))
End Function
</pre>

 

[Pesky_Weasel]

Thx Colo,

I'll give it a go.
I hate asking questions where the answer turns out to be obvious, thankfully here, this is not the case )

Pesky_Weasel

 

[Andrew Poulsom]

Colo's idea is good but I couldn't get the code to work (error 91 Object variable not set).

If you convert your text to A1 style notation, ie

'Staff Data Oct 2002'!$A$1:$AF$2500

this works:

Sub Test()
    Dim ShText As String
    Dim RngText As String
    Dim Rng As Range
    ShText = Mid(Range("A1"), 1, InStr(1, Range("A1"), "'") - 1)
    RngText = Right(Range("A1"), Len(Range("A1")) - InStr(1, Range("A1"), "!"))
    Set Rng = Sheets(ShText).Range(RngText)
End Sub

 

[Colo]

Thx Andrew,

My mistake is contain ")" as the contents of the cell .....you know.
Delete this code line
j = InStr(1, str, ")")

And Change
k = Mid(str, i + 1, j - i - 1)
to
k = mid(str, i + 1)

Might be worked. Sorry I didn't test. This is online writing.