Convert Number to Letters

[Peter Hibbs]

I am writing an Access app to interface with Excel.

I am trying to write a function in VBA which will convert a number
between 1 and 200 into the equivalent column letter in Excel i.e. A to
GR. The idea is to pass the number as a Long Int variable to the
function and it will return the equivalent letter/s code as a string.
I just can't get my head around the maths.

Any takers. The neatest solution gets a cuddly toy...maybe.

TIA

Peter Hibbs.

 

[Douglas J. Steele]

I haven't done extensive testing, but try the following:

Function NumberToLetter(InputNumber As Integer) As String
' For use with Excel.
' Converts 1 to A, 2 to B and so on to 200 to GR
' (Actually, works to 702, which is ZZ)
' Returns Error for numbers < 1 or > 702

Dim intTimes As Integer

If InputNumber > 0 Then
intTimes = (InputNumber - 1) \ 26
Select Case intTimes
Case 0
NumberToLetter = Chr$(65 + (InputNumber - 1) Mod 26)
Case 1 To 26
NumberToLetter = Chr$(65 + intTimes - 1) & _
Chr$(65 + ((InputNumber - 1) Mod 26))
Case Else
NumberToLetter = "Error"
End Select
Else
NumberToLetter = "Error"
End If

End Function

I'll take a cuddly flex grid, please! <g>

 

[Peter Hibbs]

Douglas,

Works great, thanks. Box of flex grids are in the post as I type!!

Peter.