Passing a string as a range

Pesky_Weasel

Board Regular
Joined
Sep 28, 2002
Messages
99
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,457
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

Board Regular
Joined
Sep 28, 2002
Messages
99
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 :eek:)

Pesky_Weasel
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

Code:
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,457
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,853
Messages
5,833,985
Members
430,249
Latest member
Muka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top