Convert string to range

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
I have a string variable called Bob that contains the value D2 and need it to convert it to a range called BobR. How do I do this?

I tried
Code:
Dim bob As String
Dim bobr As Range
bob = "D2"
bobr = bob

this is not an actual piece of my code exactly but I need to know how to do this in order to do my bigger piece.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
To assign a Range we would use Set, eg:

Code:
Set bobr = Range(bob)
 

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
That doesn't do anything.... it goes past in the code but the variable bobr isn't set to anything.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Please define "doesn't do anything" - what were you expecting it to do? It will set the range variable bobr to be the range represented by the string bob. It won't amend the value(s) in this range though or do anything else as the code doesn't instruct it to.
 

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61

ADVERTISEMENT

I just get an error 2029 if I hover over bobr or I get nothing stored at all in that variable.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Code:
Dim bob As String
Dim bobr As Range
bob = "D2"
Set bobr = Range(bob)

What does D2 on the active sheet contain ? #NAME? by any chance...
 

tushiroda

Board Regular
Joined
Mar 21, 2005
Messages
156

ADVERTISEMENT

erh? you did just replace the last line of your code snippet with DonkeyOte's line right?

Dim bob As String
Dim bobr As Range
bob = "D2"
Set bobr = Range(bob)
 

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
Ok, I got the code working but instead of creating a variable with the cell address it stores the value in that cell in the variable. I NEED THE ADDRESS so I can reference it in a formula later on.
 

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
Here is my real code with the problem sections in red

Code:
Sub test()

Dim c As Range
Dim R1 As Integer
Dim R2 As Integer
R1 = 2
[COLOR=DarkRed]Dim FillTo As String
Dim filltoC As String
Dim FillToR As Range[/COLOR]
Dim NewA As String

For Each c In Range("A:A")
       If Columns("A").Find(What:="Total", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate Then
        R2 = ActiveCell.Row
        FillTo = "D" & R2
        filltoC = "C" & R2
        bobr = ActiveCell.Address
        Cells(R1, 4).Select
[COLOR=DarkRed]        Set FillToR = Range(filltoC)
        ActiveCell.Formula = "=RC[-1]/" & FillToR[/COLOR]
        Range("D2").Select
        Selection.Autofill Destination:=Range("D2", FillTo), Type:=xlFillDefault
        NewA = "A" & R2
        R1 = R2 + 1
        Range(NewA).Select
        End If
Next c

End Sub
 

mmetzinger

Board Regular
Joined
Dec 30, 2010
Messages
61
OK, figured it out... this may be a totall run around but I got it work...

Code:
Sub test()

Dim c As Range
Dim R1 As Integer
Dim R2 As Integer
R1 = 2
[COLOR=DarkOrange]Dim FillTo As String
Dim filltor As String
Dim filltoC As Range[/COLOR]
Dim NewA As String

For Each c In Range("A:A")
       If Columns("A").Find(What:="Total", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate Then
        R2 = ActiveCell.Row
        FillTo = "D" & R2
[COLOR=DarkOrange]        filltor = "C" & R2
        Set filltoC = Range(filltor)[/COLOR]
        bobr = ActiveCell.Address
        Cells(R1, 4).Select
[COLOR=DarkOrange]        ActiveCell.Formula = "=RC[-1]/" & filltoC[/COLOR]
        Range("D2").Select
        Selection.Autofill Destination:=Range("D2", FillTo), Type:=xlFillDefault
        NewA = "A" & R2
        R1 = R2 + 1
        Range(NewA).Select
        End If
Next c

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,293
Messages
5,600,773
Members
414,405
Latest member
Zaurb

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