Excel

Convert between R1C1 and A1 addresses

Ease of Use

Intermediate

Version tested with

97, 2000, 2003 

Submitted by:

byundt

Description:

Function returns an address string in either R1C1 or A1 format when given an address string in the other format. 

Discussion:

Some VBA functions require addresses to be in A1 format while others require the older R1C1 style references. Although many VBA statements accept both styles of address as input, not all do. It is therefore necessary to be able to convert between the two styles. It may also be necessary to construct an address that is relative to a particular cell. For example, conditional formatting formulas are set relative to the active cell. If the active cell is not the cell receiving the conditional format formula, it won't work correctly. The Application.ConvertFormula function will perform the address onversion, but you need to specify whether the input uses A1 or R1C1 addressing. This function returns the address of a cell or range in either A1 or R1C1 style. The input address style may be either A1 or R1C1. The function is smart enough to catch invalid combinations (A1 to A1 or R1C1 to R1C1) and will return the input address. NOTE: This function may not be used in a worksheet formula! If you try, you will get an error message. 

Code:

instructions for use

			

Function R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String 'Converts input address to either A1 or R1C1 style reference relative to RefCell 'If R1C1_output is xlR1C1, then result is R1C1 style reference. 'If R1C1_output is xlA1 (or missing), then return A1 style reference. 'If RefCell is missing, then the address is relative to the active cell 'If there is an error in conversion, the function returns the input Address string Dim x As Variant If RefCell Is Nothing Then Set RefCell = ActiveCell If R1C1_output = xlR1C1 Then x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) 'Convert A1 to R1C1 Else x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) 'Convert R1C1 to A1 End If If IsError(x) Then R1C1converter = Address Else 'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula 'surrounds the address in single quotes. If Right(x, 1) = "'" Then R1C1converter = Mid(x, 2, Len(x) - 2) Else R1C1converter = x End If End If End Function

How to use:

  1. Select and copy above code (Ctrl + C).
  2. From Excel, open the Visual Basic Editor (VBE) with Alt + F11.
  3. Select any file on left, choose Insert-Module from the menu.
  4. Paste code in right pane (Ctrl + V).
  5. Close the VBE with Alt + Q.
  6. The first parameter in the function is a required string. It may be an address in either R1C1 or A1 style
  7. The second parameter is optional. It may be either the xlA1 constant (1) or the xlR1C1 constant (-4150).
  8. The third parameter is optional. It is a cell on the active worksheet to which the returned address should be relative to.
  9. Use the function in VBA code with a calling sequence like:
  10. NewAddress=R1C1converter(oldAddress,xlR1C1,Range("G5")) 'Converts oldAddress from A1 to R1C1 relative to cell G5
  11. NewAddress=R1C1converter(oldAddress) 'Converts oldAddress from R1C1 to A1
 

Test the code:

  1. Enter R1C1 style addresses in column C
  2. Press the button at the top of column C to convert those addresses to A1 style references
  3. Enter A1 style addresses in column H
  4. Press the button at the top of column H to convert those addresses to R1C1 style references
 

Sample File:

R1C1 converter.zip 10.56KB 

Approved by mdmackillop


This entry has been viewed 280 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express