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

Question

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 1

Maybe this is what you are looking for?

Answer 2

Column Numbers to Letters

Column Letters to Numbers

The good stuff is in the comments