Hi I have some code that gets the letter to the corresponding number
I'm trying to do a similar thing but not with the alphabet, only with a string of letters in the alphabet. My string is "ABCDFGHIJKMQRSUVWXYZ" 20 characters. if the number is 22 this will return "AB" and 42 will return "BB" which is also correct. However after ZZ it goes wrong. of rexample if the number is 522 the it returns "BB" Hope this makes sense, here's my code so far.
Function GetLetter(lNum As Long) As String If lNum <= 26 Then GetLetter = Chr(lNum + 64) Else GetLetter = Chr(Int((lNum - 1) / 26) + 64) & Chr(((lNum - 1) Mod 26) + 65) End If End Function
Function GetAvailableLetter(iNum As Long) As String Dim iCount As Long, iMax As Long, iPos As Long, strSearch As String strSearch = "ABCDFGHIJKMQRSUVWXYZ" iMax = Len(strSearch) If iNum > iMax Then iPos = Int((iNum - 1) / iMax) If iPos > iMax Then iPos = iNum Mod iMax GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1) End If End If iPos = iNum Mod iMax iPos = IIf(iPos = 0, iMax, iPos) GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1) End Function
This question generated 28 answers. To proceed to the answers, click here.
This thread is current as of June 27, 2014.