CODEWITHSUNDEEP
vb.netexcelcellsaspose
Reckhound
Reckhound

Reputation: 35

convert numeric to alphanumeric excel cell reference

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.

Upvotes: 2

Views: 2516

Answers (3)

Reckhound
Reckhound

Reputation: 35

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)

Upvotes: 0

Sam
Sam

Reputation: 7313

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96771

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

Upvotes: 1

Related Questions