Formula needed please (Excel VBA)

[alex_read]

Can't for the life of me think of how to get round this :rolleyes: . 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 ! :D

[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... :rolleyes:
Thanks all, those both work great ! :D