Convert Cell address.

[fraz627]

How does one go about convert the cell address.
I know that the absolute function converts the cell row, column to a cell address such as $a1 how would you convert a range a1:f10 to row and column numbers.

Thanks

 

[tyija1995]

To convert a range of formulas to absolute referencing use the following macro:

Sub AbsRef()
    Dim rng As Range
    Dim cell As Range
    Set rng = Selection
        For Each cell In rng
            If cell.HasFormula = True Then
                cell.Formula = Application.ConvertFormula(cell.Formula, _
                xlA1, xlA1, xlAbsolute)
            End If
        Next cell
End Sub

 

[mumps]

I'm not sure if I understood correctly but is this what you are looking for?

range(cells(1,1),cells(10,6))

 

[fraz627]

Sorry I wasn't so clear.
What I am looking for is a way to convert a range in the form of

Range("$D$4:$D$8" )

TO
Range(Cells(START_ROW,START_COL),Cells(END_ROW,END_COL))

Again Sorry I did not explain better

 

[jim may]

Hi Fraz627
Just to make it interesting I worked through your request, but decided to make your original range a bit more "interesting" while I was at it.
I converted the Range("$B$4:$D$8"). Paste the below code into a standard module. Before Running the below code have your Sheet1 your activesheet.

Sub Foo()
Dim Convert As String
Dim arr As Variant
Convert = Range("$B$4:$D$8").Address(ReferenceStyle:=xlR1C1)
Convert = WorksheetFunction.Substitute(WorksheetFunction.Substitute(WorksheetFunction.Substitute(Convert, "R", ""), ":", ","), "C", ",")
arr = Split(Convert, ",")
Worksheets("Sheet1").Range(Cells(CInt(arr(0)), CInt(arr(1))), Cells(CInt(arr(2)), CInt(arr(3)))).Select
End Sub

Hope this helps...

Jim

 

[fraz627]

Thanks that works great, didn't realize it was that complicated, well at least to me it is.
Thank again.

 

[Fluff]

Another option

Sub fraz627()
   Dim Sp As Variant
   Sp = Split(Replace(Range("$B$4:$D$8").Address, ":", ""), "$")
   Range(Cells(Sp(2), Sp(1)), Cells(Sp(4), Sp(3))).Select
End Sub

 

[steve the fish]

I cant think of a time when you would ever need to convert:

Range("$D$4:$D$8" )

TO
Range(Cells(START_ROW,START_COL),Cells(END_ROW,END_COL))

Why do you need to convert it? You can make the 2nd one the same as the first one if thats what you mean?

[FONT=Calibri][FONT=Calibri][SIZE=3][COLOR=#000000]Range(Cells(4,4),Cells(8,4))[/COLOR][/SIZE][/FONT][/FONT]

You can use variables if you want:

[FONT=Calibri][FONT=Calibri][SIZE=3][COLOR=#000000]fr = 4
fc = 4
lr = 8
lc = 4
Range(Cells(fr,fc),Cells(lr,lc))[/COLOR][/SIZE][/FONT][/FONT]

 

[fraz627]

What I'm trying to do is replicate the Fill command but filling the values only no formatting, I get the selected range, copy the first row, and loop by row and paste values only.


Fraz