How to get from Row, Column to Excel A1 notation?

excel vba coordinate-systems
Cade Roux picture Cade Roux · Jul 11, 2009 · Viewed 13.9k times · Source

Given a Row and Column (As Long), how can you determine the spreadsheet notation using VBA in Excel (2007):

e.g.:

(R, C) = (1, 1) -> "A1"
(R, C) = (2, 1) -> "A2"
(R, C) = (2, 2) -> "B2"

Thus if you had a function:

Function CellRef(R As Long, C As Long) As String

which provided that functionality, you could do something like:

Worksheet.Range(CellRef(R1, C1) + ":" + CellRef(R2, C2)).Copy

A little background, in case this is the wrong approach to be taking: The purpose of this is that I have a master sheet which describes other worksheets in a table:

WorksheetName, Range etc....

This master sheet controls transformations on the sheet, but the Range value is obviously in Excel notation for convenient later use in referencing the range. However a routine to manage this table, report exceptions and ensure consistency really gets things from other sheets in row and column, so for instance it gets a row and column where it knows something is starting and ending.

Here's the function I ended up with:

Private Function CellRef(R As Long, C As Long) As String
    CellRef = vbNullString
    On Error GoTo HandleError:
    CellRef = Replace(Mid(Application.ConvertFormula("=R" & R & "C" & C, XlReferenceStyle.xlR1C1, XlReferenceStyle.xlA1), 2), "$", "")
    Exit Function
HandleError:
End Function

Answer

Jimmy Chandra picture Jimmy Chandra · Jul 11, 2009

Maybe this is what you are looking for?

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More