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