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
 

Some videos you may like

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,456
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,456
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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