convert Excel column numbers into alphabetical characters

I don't think it's anything you did; it happened to me, too:

Edit: Perhaps it's just something to do with the function using Chr()?

Re: convert Excel column numbers into alphabetical character

Is there a better function to use? Or maybe I should use a different process? I want to loop through all the columns to accomplish a task.

I honestly have no idea. What are you trying to do?

The Problem that the ConvertToLetter Function has is when the first letter of a two letter column is higher then the second letter (i.e. BA, CA, CB, DA, DB, DC). This makes the iRemainder Variable greater then 26, so when it adds 64 to find the letter, it goes beyond "Z". I added a small IF statement to the function that tests for larger iRemainder values.

On closer look, it seems that the math they're using just doesn't work out consistently.

They state in that article:

The ConvertToLetter function works by using the following algorithm:
1. Divide the column number by 27, and then put the resulting integer in the variable "i".
2. Subtract the column number from "i" multiplied by 26, and then put the result in the variable "j".
3. Convert the integer values into their corresponding alphabetical characters, "i" and "j" will range from 0 to 26 respectively.

Click to expand...

But that doesn't always seem to work. Column EA for example, is column number 131. Using that number and following their process, j ends up equal to 27, which right there is out of their stated parameters in step 3.

I redid the function (essentially changing the variables so I could "see" what was going on better):
<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ColNumConvert(myCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
i = Int(myCol / 27)
j = myCol - (i * 26)
<SPAN style="color:#00007F">If</SPAN> i > 0 <SPAN style="color:#00007F">Then</SPAN> ColNumConvert = Chr(i + 64)
<SPAN style="color:#00007F">If</SPAN> j > 0 <SPAN style="color:#00007F">Then</SPAN> ColNumConvert = ColNumConvert & Chr(j + 64)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

Re: convert Excel column numbers into alphabetical character

Thanks, works great!

Re: convert Excel column numbers into alphabetical character

Hi,

Another option might be:


HTH

Alan

Re: convert Excel column numbers into alphabetical character

old post maybe but hey:

excel.Workbooks.application.WindowState = XlWindowState.xlMinimized
MsgBox application.Substitute(activecell.Rows.address(0, 0), selection.row(), "") & Space(10), vbQuestion 'yes

or for a cell? but functions above maybe better? (formula in row 20, then 20 disappears),
=SUBSTITUTE(SUBSTITUTE(CELL("address",$EK20),"$",""),ROW(),"")


i've been using this formula alot to id cells, columns rows for vb static cells eg A1-Z8 with dim's, for dynamic references.
Dim J6 As String: J6 = RANGE("J6")

J6 has:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$BK6),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$BK6),"$",""),ROW(),"")

Re: convert Excel column numbers into alphabetical character

yep well.. sorry i spend a lot of time researching items.. posts what ever. only getting 10 minutes is just silly.
(well.. since i have more time will have to remember not to ?????, later; i might use 4 hours just to research an item.. do you want deficient posts then? What!? ah: many posts less content, I cant get 1 hour???? make it 4 please)

under:
Private Sub Worksheet_Change(ByVal Target As excel.RANGE)
an example for how might use above under selection change, then might be:


If Not Intersect(Me.RANGE(J6), .Cells) Is Nothing Then 'SKEL NEW
If Cells(activecell.row, D2) = vbNullString Then 'home
Cells(activecell.row, D2).Select 'jump
End If
application.EnableEvents = True: goMODE 'EVENTS
End If