VBA convert A1 reference to R1C1 reference

N

neveu

Board Regular
Joined
Jan 27, 2009
Messages
225
hello forum,

i have a list of references that i would like to convert to R1C1 reference.
for example for E51 to have 51,5 so then i could use these in my VBA code.
any ideas?

here is the entire list to be converted
E51
M51
S51
E53
S53
E55
E59
L59
AA59
E61
T61
E67
L67
T67
E69
Z69
E71
O71
 
Sort by date Sort by votes
=cell("address", indirect(address(R,C)))
R is your row number, C = column number
result = $B$6

reverse

=cell("row", A1CellRef) & cell("col", A1CellRef)
A1CellRef = B6
result = R6C2
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
A VBA Solution:


If you have strings:

Function Address_RC( byval rng as String) as String
Address_RC = "R" & Range(rng).row & "C" & Range(rng).Column
End Function

If you have range objects:

Function Address_RC( byref rng as Range) as String
Address_RC = "R" & rng.row & "C" & rng.Column
End Function
 
Last edited:
Upvote 0
Code: 
Function A1ToR1C1(addr As String) As String
    Dim rngStr As String
    rngStr = Range(addr).Address(ReferenceStyle:=xlR1C1)
    
    A1ToR1C1 = rngStr
End Function

Input: A1ToR1C1("A5")
Output: R5C1

Input: A1ToR1C1("A1:E5")
Output: R5C1
 
Upvote 0
Hi Folks,
I ran into some issues converting address from R1C1 to A1. pgc01 solved my problem! frozenprakesh also helped. I decided to carry out an investigation using VBA, Here some of the built-in functions I have been using to convert addresses from A1 to R1C1 and vice versa. I hope this helps other users programming Excel in VBA and using R1C1 to reference cells.

VBA Code: 
Public Sub DemonstrateConvertions()
   ' https://www.mrexcel.com/board/threads/vba-convert-a1-reference-to-r1c1-reference.593703/
   ' Demonsterate conversion of addresses from A1 to R1C1 aand back)

   Dim R As Range
   Debug.Print "--- Convert Address ---"
   Set R = ActiveSheet.Range("$E$90")
   Debug.Print R.Address
   Debug.Print "1. Address in A1  -> " & R.Address(ReferenceStyle:=xlA1)
   Debug.Print "2. Address in R1C1 -> " & R.Address(ReferenceStyle:=xlR1C1)
   Debug.Print "3. Convert R1C1 to A1 -> " & Range(Cells(90, 5), Cells(90, 5)).Address(ReferenceStyle:=xlA1)
   Debug.Print "4. Convert A1 to R1C1 -> " & Range("E90").Address(ReferenceStyle:=xlR1C1) & vbCrLf
   
   Dim R2 As Range
   Set R2 = Range(Cells(90, 5).EntireColumn.Address)
   Debug.Print R2.Address & " Defined using Range(Cells(90, 5).EntireColumn.Address)"
   Debug.Print "5. Column Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "6. Column Address in R1C1 -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf
   
   Set R2 = Range(R.EntireColumn.Address)
   Debug.Print R2.Address & " Defined using Range(Range.EntireColumn.Address)"
   Debug.Print "7. Column Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "8. Column Address in A1  -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf
   
   Set R2 = Range(R.EntireRow.Address)
   Debug.Print R2.Address; " Defined using Range(Range.EntireRow.Address)"
   Debug.Print "9. Row Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "10. Row Address in A1  -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf
   
   Set R2 = Range(Cells(2, 8), Cells(17, 14))
   Debug.Print R2.Address & " Defined using Range(Cells(2, 8), Cells(17, 14))"
   Debug.Print "11. Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "12. Address in A1  -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf

   Debug.Print "--- Convert Formula ---"
   Debug.Print "Formula defined using R.Formula = ""= F90 + G90 "" ... Or ..."
   Debug.Print "R.Formula = ""=RC[1]+RC[2]"" Both produce identical results."
   Debug.Print "NB: R.Address = " & R.Address
   
   R.Formula = "=F90+G90"
   Debug.Print "13. R.Formula >> " & R.Formula
   Debug.Print "14. R.Formula2R1C1 >> " & R.Formula2R1C1
   Debug.Print "15. R.Formula2R1C1Local >> " & R.Formula2R1C1Local & vbCrLf
   
   Debug.Print "--- Using Application.Convert ---"
   
   Debug.Print "16. " & Application.ConvertFormula( _
               Formula:="=RC[1]+RC[2]", _
               fromReferenceStyle:=xlR1C1, _
               toReferenceStyle:=xlA1, _
               ToAbsolute:=xlAbsolute, _
               Relativeto:=R)
               
   Debug.Print "17. " & Application.ConvertFormula( _
               Formula:="=RC[1]+RC[2]", _
               fromReferenceStyle:=xlR1C1, _
               toReferenceStyle:=xlA1, _
               ToAbsolute:=xlRelative, _
               Relativeto:=R)
               
   Debug.Print "18. " & Application.ConvertFormula("=f90+g90", xlA1, xlR1C1, xlAbsolute, R)
   Debug.Print "19. " & Application.ConvertFormula("=f90+g90", xlA1, xlR1C1, xlRelative, R) & vbCrLf
   
   Debug.Print "------ Demonstrate Overloading of Arguments in Range Function ----"
   Debug.Print "20. Range(Cells(1, 1), Cells(3, 4)).Address = " & _
                    Range(Cells(1, 1), Cells(3, 4)).Address
   Debug.Print "21. Range(Cells(1, 1).Address, Cells(3, 4).Address).Address = " & _
                    Range(Cells(1, 1).Address, Cells(3, 4).Address).Address
                    
   Debug.Print "22. Range(Cells(1, 1)).Address  <--- **** RUNTIME ERROR ***"
   Debug.Print "23. Range(Cells(1, 1).Address).Address  <--- * OK * = " & _
                    Range(Cells(1, 1).Address).Address
End Sub
 
Upvote 0
Samuel Lount said:
Hi Folks,
I ran into some issues converting address from R1C1 to A1. pgc01 solved my problem! frozenprakesh also helped. I decided to carry out an investigation using VBA, Here some of the built-in functions I have been using to convert addresses from A1 to R1C1 and vice versa. I hope this helps other users programming Excel in VBA and using R1C1 to reference cells.

VBA Code: 
Public Sub DemonstrateConvertions()
   ' https://www.mrexcel.com/board/threads/vba-convert-a1-reference-to-r1c1-reference.593703/
   ' Demonsterate conversion of addresses from A1 to R1C1 aand back)

   Dim R As Range
   Debug.Print "--- Convert Address ---"
   Set R = ActiveSheet.Range("$E$90")
   Debug.Print R.Address
   Debug.Print "1. Address in A1  -> " & R.Address(ReferenceStyle:=xlA1)
   Debug.Print "2. Address in R1C1 -> " & R.Address(ReferenceStyle:=xlR1C1)
   Debug.Print "3. Convert R1C1 to A1 -> " & Range(Cells(90, 5), Cells(90, 5)).Address(ReferenceStyle:=xlA1)
   Debug.Print "4. Convert A1 to R1C1 -> " & Range("E90").Address(ReferenceStyle:=xlR1C1) & vbCrLf
  
   Dim R2 As Range
   Set R2 = Range(Cells(90, 5).EntireColumn.Address)
   Debug.Print R2.Address & " Defined using Range(Cells(90, 5).EntireColumn.Address)"
   Debug.Print "5. Column Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "6. Column Address in R1C1 -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf
  
   Set R2 = Range(R.EntireColumn.Address)
   Debug.Print R2.Address & " Defined using Range(Range.EntireColumn.Address)"
   Debug.Print "7. Column Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "8. Column Address in A1  -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf
  
   Set R2 = Range(R.EntireRow.Address)
   Debug.Print R2.Address; " Defined using Range(Range.EntireRow.Address)"
   Debug.Print "9. Row Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "10. Row Address in A1  -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf
  
   Set R2 = Range(Cells(2, 8), Cells(17, 14))
   Debug.Print R2.Address & " Defined using Range(Cells(2, 8), Cells(17, 14))"
   Debug.Print "11. Address in A1  -> " & R2.Address(ReferenceStyle:=xlA1)
   Debug.Print "12. Address in A1  -> " & R2.Address(ReferenceStyle:=xlR1C1) & vbCrLf

   Debug.Print "--- Convert Formula ---"
   Debug.Print "Formula defined using R.Formula = ""= F90 + G90 "" ... Or ..."
   Debug.Print "R.Formula = ""=RC[1]+RC[2]"" Both produce identical results."
   Debug.Print "NB: R.Address = " & R.Address
  
   R.Formula = "=F90+G90"
   Debug.Print "13. R.Formula >> " & R.Formula
   Debug.Print "14. R.Formula2R1C1 >> " & R.Formula2R1C1
   Debug.Print "15. R.Formula2R1C1Local >> " & R.Formula2R1C1Local & vbCrLf
  
   Debug.Print "--- Using Application.Convert ---"
  
   Debug.Print "16. " & Application.ConvertFormula( _
               Formula:="=RC[1]+RC[2]", _
               fromReferenceStyle:=xlR1C1, _
               toReferenceStyle:=xlA1, _
               ToAbsolute:=xlAbsolute, _
               Relativeto:=R)
              
   Debug.Print "17. " & Application.ConvertFormula( _
               Formula:="=RC[1]+RC[2]", _
               fromReferenceStyle:=xlR1C1, _
               toReferenceStyle:=xlA1, _
               ToAbsolute:=xlRelative, _
               Relativeto:=R)
              
   Debug.Print "18. " & Application.ConvertFormula("=f90+g90", xlA1, xlR1C1, xlAbsolute, R)
   Debug.Print "19. " & Application.ConvertFormula("=f90+g90", xlA1, xlR1C1, xlRelative, R) & vbCrLf
  
   Debug.Print "------ Demonstrate Overloading of Arguments in Range Function ----"
   Debug.Print "20. Range(Cells(1, 1), Cells(3, 4)).Address = " & _
                    Range(Cells(1, 1), Cells(3, 4)).Address
   Debug.Print "21. Range(Cells(1, 1).Address, Cells(3, 4).Address).Address = " & _
                    Range(Cells(1, 1).Address, Cells(3, 4).Address).Address
                   
   Debug.Print "22. Range(Cells(1, 1)).Address  <--- **** RUNTIME ERROR ***"
   Debug.Print "23. Range(Cells(1, 1).Address).Address  <--- * OK * = " & _
                    Range(Cells(1, 1).Address).Address
End Sub
Click to expand...

Here is the output of VBA Sub DemonstrateConvertions():

VBA Code: 
--- Convert Address ---
$E$90
1. Address in A1  -> $E$90
2. Address in R1C1 -> R90C5
3. Convert R1C1 to A1 -> $E$90
4. Convert A1 to R1C1 -> R90C5

$E:$E Defined using Range(Cells(90, 5).EntireColumn.Address)
5. Column Address in A1  -> $E:$E
6. Column Address in R1C1 -> C5

$E:$E Defined using Range(Range.EntireColumn.Address)
7. Column Address in A1  -> $E:$E
8. Column Address in A1  -> C5

$90:$90 Defined using Range(Range.EntireRow.Address)
9. Row Address in A1  -> $90:$90
10. Row Address in A1  -> R90

$H$2:$N$17 Defined using Range(Cells(2, 8), Cells(17, 14))
11. Address in A1  -> $H$2:$N$17
12. Address in A1  -> R2C8:R17C14

--- Convert Formula ---
Formula defined using R.Formula = "= F90 + G90 " ... Or ...
R.Formula = "=RC[1]+RC[2]" Both produce identical results.
NB: R.Address = $E$90
13. R.Formula >> =F90+G90
14. R.Formula2R1C1 >> =RC[1]+RC[2]
15. R.Formula2R1C1Local >> =RC[1]+RC[2]

--- Using Application.Convert ---
16. =$F$90+$G$90
17. =F90+G90
18. =R90C6+R90C7
19. =RC[1]+RC[2]

------ Demonstrate Overloading of Arguments in Range Function ----
20. Range(Cells(1, 1), Cells(3, 4)).Address = $A$1:$D$3
21. Range(Cells(1, 1).Address, Cells(3, 4).Address).Address = $A$1:$D$3
22. Range(Cells(1, 1)).Address  <--- **** RUNTIME ERROR ***
23. Range(Cells(1, 1).Address).Address  <--- * OK * = $A$1
 
Upvote 0
Samuel Lount said:
Here is the output of VBA Sub DemonstrateConvertions():

VBA Code: 
--- Convert Address ---
$E$90
1. Address in A1  -> $E$90
2. Address in R1C1 -> R90C5
3. Convert R1C1 to A1 -> $E$90
4. Convert A1 to R1C1 -> R90C5

$E:$E Defined using Range(Cells(90, 5).EntireColumn.Address)
5. Column Address in A1  -> $E:$E
6. Column Address in R1C1 -> C5

$E:$E Defined using Range(Range.EntireColumn.Address)
7. Column Address in A1  -> $E:$E
8. Column Address in A1  -> C5

$90:$90 Defined using Range(Range.EntireRow.Address)
9. Row Address in A1  -> $90:$90
10. Row Address in A1  -> R90

$H$2:$N$17 Defined using Range(Cells(2, 8), Cells(17, 14))
11. Address in A1  -> $H$2:$N$17
12. Address in A1  -> R2C8:R17C14

--- Convert Formula ---
Formula defined using R.Formula = "= F90 + G90 " ... Or ...
R.Formula = "=RC[1]+RC[2]" Both produce identical results.
NB: R.Address = $E$90
13. R.Formula >> =F90+G90
14. R.Formula2R1C1 >> =RC[1]+RC[2]
15. R.Formula2R1C1Local >> =RC[1]+RC[2]

--- Using Application.Convert ---
16. =$F$90+$G$90
17. =F90+G90
18. =R90C6+R90C7
19. =RC[1]+RC[2]

------ Demonstrate Overloading of Arguments in Range Function ----
20. Range(Cells(1, 1), Cells(3, 4)).Address = $A$1:$D$3
21. Range(Cells(1, 1).Address, Cells(3, 4).Address).Address = $A$1:$D$3
22. Range(Cells(1, 1)).Address  <--- **** RUNTIME ERROR ***
23. Range(Cells(1, 1).Address).Address  <--- * OK * = $A$1
Click to expand...

*** Typos*** in output above are corrected below:
VBA Code: 
--- Convert Address ---
$E$90
1. Address in A1  -> $E$90
2. Address in R1C1 -> R90C5
3. Convert R1C1 to A1 -> $E$90
4. Convert A1 to R1C1 -> R90C5

$E:$E Defined using Range(Cells(90, 5).EntireColumn.Address)
5. Column Address in A1  -> $E:$E
6. Column Address in R1C1 -> C5

$E:$E Defined using Range(Range.EntireColumn.Address)
7. Column Address in A1  -> $E:$E
8. Column Address in R1C1  -> C5

$90:$90 Defined using Range(Range.EntireRow.Address)
9. Row Address in A1  -> $90:$90
10. Row Address in R1C1  -> R90

$H$2:$N$17 Defined using Range(Cells(2, 8), Cells(17, 14))
11. Address in A1  -> $H$2:$N$17
12. Address in R1C1  -> R2C8:R17C14

--- Convert Formula ---
Formula defined using R.Formula = "= F90 + G90 " ... Or ...
R.Formula = "=RC[1]+RC[2]" Both produce identical results.
NB: R.Address = $E$90
13. R.Formula >> =F90+G90
14. R.Formula2R1C1 >> =RC[1]+RC[2]
15. R.Formula2R1C1Local >> =RC[1]+RC[2]

--- Using Application.Convert ---
16. =$F$90+$G$90
17. =F90+G90
18. =R90C6+R90C7
19. =RC[1]+RC[2]

------ Demonstrate Overloading of Arguments in Range Function ----
20. Range(Cells(1, 1), Cells(3, 4)).Address = $A$1:$D$3
21. Range(Cells(1, 1).Address, Cells(3, 4).Address).Address = $A$1:$D$3
22. Range(Cells(1, 1)).Address  <--- **** RUNTIME ERROR ***
23. Range(Cells(1, 1).Address).Address  <--- * OK * = $A$1
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Row Ranking Not Working Correctly
Replies
3
Views
197
Fluff
Fluff
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
223
NoSparks
N
K
  • Solved
Excel Percentage formulas and Pivot table chart displays
3 4 5
Replies
48
Views
2K
kellym9236
K
P
  • Solved
Computing investment return formula
Replies
8
Views
435
polarwoc
P
C
  • Solved
Issue with 00:00 time when using an IF function
Replies
3
Views
395
jtakw
jtakw

Forum statistics

Threads
1,214,994
Messages
6,122,633
Members
449,092
Latest member
bsb1122

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