Convert string to range

M

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.
 
Sort by date Sort by votes
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.
 
Upvote 0
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...
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Searches using a textbox for input
Replies
9
Views
129
LegendaryAxe
L
D
  • Question Question
Vba to create a new excel file and convert all cell formulas to text
Replies
3
Views
169
Candyman8019
C
S
  • Question Question
Excel Drop Down Multiple Select/Remove & Auto Alphabetize Selections
Replies
0
Views
388
Sactown Steve
S
C
  • Solved
Help! Can't pass a range of cell strings into a variant array/can't reference the strings in this array - myarray(I,1) gives subscript error
Replies
3
Views
112
Peter_SSs
Peter_SSs
B
  • Solved
Copy a single selected word in a cell into memory using vba /or sendkeys
Replies
3
Views
250
jolivanes
jolivanes

Share this page

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
Back
Top