convert numeric to alphanumeric excel cell reference

Question

How can I convert from numeric to alphanumeric cell references? For example, I have the numeric row,col(0,1) and I want to convert to a standard MS Excel alphanumeric col,row(B,1)? I'm sorry, but I don't know the proper terminology to describe different cell references.

I want to write VB.NET code with numeric cell references so my code can iterate but convert to alphanumeric so I can insert formulas into my spreadsheet.

Answer 1

To convert from a numerical column designator to a alphabetic designator, consider:

Sub qwerty()
    n = 134
    s = Cells(1, n).Address(0, 0)
    MsgBox Left(s, Len(s) - 1)
End Sub

EDIT#1:

For a function to perform the conversion:

Public Function ColumnId(N As Long) As String
    s = Cells(1, N).Address(0, 0)
    ColumnId = Left(s, Len(s) - 1)
End Function

Answer 2

If you want to get the full address. Then you can just use the .Address property of a range.

If you want to separate the row and column then you can split the address into the individual parts using Split on the $.

Sub RangeAddressTest()

Dim cell As Range
Dim fullAddress As String
Dim rowAddress As String, columnAddress As String

Dim detailsArray As Variant

'select your cell
Set cell = ActiveSheet.Cells(30, 25)



fullAddress = cell.Address

detailsArray = Split(fullAddress, "$")

columnAddress = detailsArray(1)
rowAddress = detailsArray(2)


MsgBox "Full Address: " & fullAddress _
        & vbCrLf & vbCrLf & _
       "Column Address: " & columnAddress _
        & vbCrLf & vbCrLf & _
       "Row Address: " & rowAddress

End Sub

Answer 3

Thanks for your answers to my question, both seem like they should work but while looking around I found a very simple answer from the Aspose forum that gets the job done with two lines of code. Thanks for your ideas - I learn more by seeing different ways of getting to the same solution.

Aspose Forum: As per my understanding, you wish to get the cells reference in the syntax of "A1" (Cell Name). You may consider using the following code snippet that returns the alphanumeric cell reference for cell[0,0].

VB

Dim r As Integer = 0, c As Integer = 0

Dim Cell As String = CellsHelper.ColumnIndexToName(c) + (r + 1)