Excel is pretty flexible. You can use numbers instead of letter in expressions
like:

activesheet.cells(1,"A").value = "hi there"
or
activesheet.cells(1,1).value = "hi there"

So you may not need to do the conversion at all.

But if you must, this worked ok for me:

Option Explicit
Private Sub column()
Dim col As Integer
For col = 1 To 55
Debug.Print Str(col) & " " & ConvertToLetter(col)
Next col
End Sub
Function ConvertToLetter(iCol As Integer) As String
Dim myStr As String
myStr = Worksheets(1).Cells(1, iCol).Address(False, False)
myStr = Application.Substitute(myStr, "1", "")

ConvertToLetter = myStr
End Function


jeff wrote:

Hi gurus!

I need to use column numbers in a loop and convert them to the column
letters for populating cells.

I initially wrote a conversion function but found it failing in transition
from Z to AA and AZ to BA. So I scoured the net and got a couple of
conversion functions...all of which failed including this one from Microsoft
Knowledge base...

Private Sub column()
Dim col As Integer
For col = 1 To 55
Debug.Print Str(col) & " " & ConvertToLetter(col)
Next col
End Sub

Function ConvertToLetter(iCol As Integer) As String
' FROM MICROSOFT
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

It correctly converts but fails around the transition points:-

52 AZ
53 A[
54 BB
.
78 BZ
79 B[
80 B\
81 CC

As you can see it corrects itself as 54 is indeed BB and 81 is CC and as can
be seen it progressively gets worse...
Anyone any ideas on a foolproof conversion? I have to count up to 256 (IV)
cheers
Jeff

--

Dave Peterson