Thursday, February 18, 2010

ASCII code converter : Column Number to Alphabetical reference

The below function be used to convert any Column Number into Alphabets. example :- If you pass 1 to the function, it will Return A. If you pass 26, It will return Z. If you pass 27 It will return AA....and so on.

'ASCII converter
Public Function convert_asc(Target As Integer) As String

Dim high, low As Integer
Dim temp As String
high = Int(Target / 26)
low = Target Mod 26
temp = ""
If high > 0 Then
temp = Chr(high + 64)
End If
temp = temp & Chr(low + 64)
If Target Mod 26 = 0 Then
high = Int(Target / 26)
temp = ""
If high = 1 Then
temp = "Z"
Else
temp = Chr(high + 63) & "Z"
End If
End If
convert_asc = temp
End Function

2 comments:

  1. I get the same results using the below mentioned function.

    Function GetColumn(Pr)
    Dim Rn As Range
    Set Rn = ActiveSheet.Cells(1, Pr)
    GetColumn = Mid(Rn.Address, 2, InStr(2, Rn.Address, "$") - 2)
    End Function

    ReplyDelete