Formula needed please (Excel VBA)

[alex_read]

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

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

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

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 !

[gravyboy]

Try this

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)

[Si_the_geek]

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

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

[gravyboy]

OK so I did misinterpret that bit . . .

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.

[alex_read]

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