Converting Numeric Columns to Excel Alpha Notation

J

jeff

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
 
A

avveerkar

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

Veerkar

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
Click to expand...
 
D

Dave Peterson

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

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
Click to expand...
 
J

jeff

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 said:
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

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
Click to expand...
Click to expand...
 
J

jeff

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


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

Veerkar

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
Click to expand...
Click to expand...
 
D

Dave Peterson

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!)
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Functions to get range properties 2
Help to correct code 6
Get Address of Current Cell 7
Function to Sum colored cells values not working 4
vb project data exported to excel 2
Looping thru columns beyond Z 3
UDF + range object and Offset formula to Value problem 5
Help creating object in excel VBA 2

Top