Calc the num Equivalent for Excel Cols

Jeice

I want to write code so in the end I get a table that looks like the following in Excel

1 = A
2 = B

27 = AA
256 = IV

I was able to do this using modulus and division, a for loop, and 2 big case statements. But, I'm pretty sure there's a better way of doing this. Like, I've seen the function before where you can define all the letters in a string, something like:

alphanumeric = ("ABCDE....Z")

Does anyone know a better way than my way? I'm using the excel vb editor

My Code:

Sub test()
Dim i As Long
Dim word As String

For i = 1 To 256
word = calc(i)
Cells(i, 1).Value = i
Cells(i, 2).Value = word
Next i

End Sub

Function calc(num As Long) As String
Dim remainder As Long
Dim whole As Integer

Dim first As String
Dim second As String
Dim text As String

first = ""
second = ""

remainder = num Mod 26

If num > 26 Then
whole = Int(num / 26)
Else
whole = num
End If

Select Case whole
Case 1
first = "A"
Case 2
first = "B"
// al the way to Z
End Select


If num > 26 Then
Select Case remainder
Case 1
second = "A"
// all the way to Z
End Select

text = first + second
Else
text = first
End If

calc = text
End Function

Jobastion

You can get rid of the case statements, and just put in

first = Chr(whole + 64)
second = Chr(remainder + 64)

in their places.
Chr directly converts a number into a character, and the capitol letters start at 65. [strike]So "whole" and "remainder" both run from 1:26 + 64 = 65:90 and that converts to A:ZMostly you can. Except that numbers with no remainder like 52, do not approve of this method. I'll figure a fix and update.[/strike]
Then, because numbers that should end in Z are divisable by 26 evenly, and thus move the "whole" counter up by one, and have no remainder, (and thus 0 + 64 = @ with the chr function), add this after the whole/remainder calculation, but before calculating "first".

If remainder = 0 Then
whole = whole - 1
remainder = 26
End If

Orogogus

If it doesn't have to be done in VB code, then you could do something like the following:

1. Column A: 1, 2, 3, etc.
2. Some cell (I used J1): ABCDEFGHIJKLMNOPQRSTUVWXYZ
3. Column B: =FLOOR((A1-1)/26, 1)
4. Column C: =MOD(A1-1,26)+1
5. Column =IF(B1>0, MID(J$1,B1,1), "")
6. Column E: =MID(J$1,C1,1)
7. Column F: =CONCATENATE(D1,E1)

You could combine these into fewer columns, of course, and/or move the string into one of the functions

Jeice

@ Jobastion: sweet, that shortens my code significantly. Thank you

@ Orogogus: cool, thanks, but since I have most of the code already, I'mma go with Jobastion's suggestion.