Thanks a heap....cant always trust microsoft code can we....or mine for that
matter...;)
cheers
Jeff


"avveerkar" wrote
in message ...

Change one line in your function iAlpha = Int(iCol / 27) to
iAlpha=Int((iCol-1)/26)

Veerkar


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


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile:
http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=527894