Formula needed please (Excel VBA)

Can't for the life of me think of how to get round this :rolleyes: . Here goes ...

Quote:

---

| A | B | C |
---------------------
| 1 | 2 | 3 |

---

In Excel, I can use the following on the above :

Code:

---

Dim intCounter as integer, strMessage as String

For intcounter = 1 to ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    strMessage = strMessage & Range(chr(intcounter +64) & "1") & " "
Next
 
Msgbox strMessage

---

This takes the intcounter number(index of the selected column) & converts its Ascii value to a character - the column letter (A - Z).
This crashes though, when columns past Z are reached (getting into AA, AB, BA, BB, AAA etc). Can anyone think of a formula or loop I can put in to allow for these extra columns please ?

Thanks guys ! :D

Try this

VB Code:

Dim intCounter as integer, strMessage as String, intOverCounter as Integer
Dim intOverCounter as Integer, intModAlpha as Integer
 
Const LASTALPHA = 26
 
For intcounter = 1 to ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
 
    If intCounter > LASTALPHA Then
        If intCounter > LASTALPHA Then
             intModAlpha = intCounter Mod LASTALPHA
             If intModAlpha = 0 Then
                  intOverCounter = intCounter / LASTALPHA
             Else
                  intOverCounter = ((intCounter - (intCounter Mod LASTALPHA)) / LASTALPHA)
             End If
        End If
 
        strMessage = strMessage & Range(chr((intOverCounter) +64) & "1") & Range(chr((intcounter- (LASTALPHA*intOverCounter) +64) & "1")  & " "
Next
 
Msgbox strMessage

I may have misinterpreted the strMessage bit, but basically you need to have two bits if the intCounter is over 26 (ie Z)

Easiest way is not to use "range" but "cells" instead, as this doesn't need the letters! ;)

VB Code:

Dim intCounter as integer, strMessage as String
 
For intcounter = 1 to ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    strMessage = strMessage & cells(1,intcounter) & " "
Next
 
Msgbox strMessage

OK so I did misinterpret that bit . . .

VB Code:

Dim intCounter as integer, strMessage as String, intOverCounter as Integer
Dim intOverCounter as Integer, intModAlpha as Integer
 
Const LASTALPHA = 26
 
For intcounter = 1 to ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
 
    If intCounter > LASTALPHA Then
        If intCounter > LASTALPHA Then
             intModAlpha = intCounter Mod LASTALPHA
             If intModAlpha = 0 Then
                  intOverCounter = intCounter / LASTALPHA
             Else
                  intOverCounter = ((intCounter - (intCounter Mod LASTALPHA)) / LASTALPHA)
             End If
        End If
 
        If intOverCounter Then
             strMessage = strMessage & Range(chr$((intOverCounter) +64) & chr$((intcounter- (LASTALPHA*intOverCounter) +64) & "1") & " "
        Else
             strMessage = strMessage & Range(chr(intcounter +64) & "1") & " "
        End If
 
Next
 
Msgbox strMessage

Although I would go with the Cells option personally . . . I just wanted to clarify what I wass saying.

I do like making things difficult for myself... :rolleyes:
Thanks all, those both work great ! :D