CODEWITHSUNDEEP
vba
Chad Portman
Chad Portman

Reputation: 1216

Converting a Number to Alphanumeric in VBA

I am using the below code as a portion of a much larger code to convert a number to its alphanumeric equal i.e 1=A, 2=B, etc. While this does work it is crazy long code and I am sure there is a better way to do this and was hoping you guys could assist.

Sub Convert()

Time = Range("A1")

If Time = 1 Then
    E = "A"
Else
If Time = 2 Then
    E = "B"
Else
If Time = 3 Then
    E = "C"
Else
If Time = 4 Then
    E = "D"
Else
If Time = 5 Then
    E = "E"
Else
If Time = 6 Then
    E = "F"
Else
If Time = 7 Then
    E = "G"
Else
If Time = 8 Then
    E = "H"
Else
If Time = 9 Then
    E = "I"
Else
If Time = 10 Then
    E = "J"
Else
If Time = 11 Then
    E = "K"
Else
If Time = 12 Then
    E = "L"
Else
If Time = 13 Then
    E = "M"
Else
If Time = 14 Then
    E = "N"
Else
If Time = 15 Then
    E = "O"
Else
If Time = 16 Then
    E = "P"
Else
If Time = 17 Then
    E = "Q"
Else
If Time = 18 Then
    E = "R"
Else
If Time = 19 Then
    E = "S"
Else
If Time = 20 Then
    E = "T"
Else
If Time = 21 Then
    E = "U"
Else
If Time = 22 Then
    E = "V"
Else
If Time = 23 Then
    E = "W"
Else
If Time = 24 Then
    E = "X"
Else
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

MsgBox E



End Sub

Upvotes: 0

Views: 4203

Answers (2)

tospig
tospig

Reputation: 8333

This is one way. 

Sub numberToLetter()

    Dim Time As Integer
    Dim E As String

    Time = Range("A1")

    If Time > 26 Then
        E = Chr(Int((Time - 1) / 26) + 64) & Chr(((Time - 1) Mod 26) + 65)
    Else
        E = Chr(Time + 64)
    End If

End Sub

Notes

Chr returns a character based on the ASCII value

Upvotes: 1

Matteo NNZ
Matteo NNZ

Reputation: 12645

Easier and more solid way is to use what Excel already offers, which means "every letter is associated to a number in the ranges": 

Public Function numberToLetter(ByVal myNumber As Integer) As String
    numberToLetter = Split(Cells(1, myNumber).Address, "$")(1)
End Function

Upvotes: 2

Related Questions