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

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

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

Thanks Dave

But I needed to let you know where I was using it, see following code
snippet...

'set previous and current columns and calculate Excel Column
Alphas
lintPrevCol = lintCurCol
lintCurCol = lintCurCol + (Kount - lintConCol - 1)
lstrStartCol = ConvertToLetter(lintPrevCol + 1)
lstrEndCol = ConvertToLetter(lintCurCol)

'output Connection Headings
lstrStartCell = lstrStartCol & "1"
lstrEndCell = lstrEndCol & "1"
oSheet.Range(lstrStartCell, lstrEndCell) = lstraFields

'output Connection Data
lstrStartCell = lstrStartCol & lstrRow
lstrEndCell = lstrEndCol & lstrRow
oSheet.Range(lstrStartCell, lstrEndCell) = lstraData

cheers
jeff

"Dave Peterson" wrote in message
...
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

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

Instead of this:

oSheet.Range(lstrStartCell, lstrEndCell) = lstraData

You could use:

with oSheet
.range(.cells(1, lintPrevCol + 1), .cells(1,lintCurCol)) =lstraData
end with

(check to see if I got the right assignments!)

jeff wrote:

Thanks Dave

But I needed to let you know where I was using it, see following code
snippet...

'set previous and current columns and calculate Excel Column
Alphas
lintPrevCol = lintCurCol
lintCurCol = lintCurCol + (Kount - lintConCol - 1)
lstrStartCol = ConvertToLetter(lintPrevCol + 1)
lstrEndCol = ConvertToLetter(lintCurCol)

'output Connection Headings
lstrStartCell = lstrStartCol & "1"
lstrEndCell = lstrEndCol & "1"
oSheet.Range(lstrStartCell, lstrEndCell) = lstraFields

'output Connection Data
lstrStartCell = lstrStartCol & lstrRow
lstrEndCell = lstrEndCol & lstrRow
oSheet.Range(lstrStartCell, lstrEndCell) = lstraData

cheers
jeff

"Dave Peterson" wrote in message
...
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

--

Dave Peterson