Convert string to range

[mmetzinger]

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

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.

 

[DonkeyOte]

To assign a Range we would use Set, eg:

Set bobr = Range(bob)

 

[mmetzinger]

That doesn't do anything.... it goes past in the code but the variable bobr isn't set to anything.

 

[Richard Schollar]

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]

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

 

[DonkeyOte]

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]

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]

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]

Here is my real code with the problem sections in red

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]

OK, figured it out... this may be a totall run around but I got it work...

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