RE: Better way of determining resulting Excel column from integer?

On 2016-08-16 17:25, Darren wrote:

This is what I use. It can be refined for sure but it does work.

PROCEDURE spnCol2cCol

LPARAMETERS vnCol

LOCAL lnInt1, lnInt2, lnInt3, lcColumn

STORE "" TO lcColumn

lcColumn = ""

DO CASE

 CASE m.vnCol > 1378

   STORE m.vnCol - 703 TO vnCol

   m.lnInt1 = INT(m.vnCol / 676) + 1  && Plus 1 for the 702 

component

   STORE m.vnCol - (m.lnInt1 - 1) * 676 TO m.vnCol

   STORE INT(m.vnCol / 26) + 1 TO lnInt2
   STORE mod(m.vnCol, 26)  + 1 TO lnInt3

   lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 +

m.lnInt3)

 CASE m.vnCol > 702

   lnInt1 = 1  && "A"
   lnInt2 = CEILING((m.vnCol - 702) / 26)
   lnInt3 = m.vnCol - 702 - (m.lnInt2 - 1) * 26

   lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2) + CHR(64 +

m.lnInt3)

 CASE m.vnCol > 26

    lnInt1   = INT((m.vnCol - 1) / 26)
    lnInt2   = m.vnCol - m.lnInt1 * 26
    lcColumn = CHR(64 + m.lnInt1) + CHR(64 + m.lnInt2)

 OTHERWISE

    lcColumn = CHR(64 + m.vnCol)

ENDCASE

RETURN lcColumn

ENDPROC

Hmmm....thanks, but I think I'll stick with mine or the ADDRESS function in Excel.

FUNCTION GetColumn(tiNumber as Integer) as String LOCAL liMultiples as Integer, lcColumn as String, liLeftover as Integer liMultiples = INT((tiNumber-1)/26) liLeftover = MOD(tiNumber,26) IF liLeftover <> 0 THEN lcColumn = CHR(liLeftover+64) ELSE lcColumn = "Z" ENDIF IF liMultiples >= 1 THEN lcColumn = CHR(liMultiples + 64) + lcColumn ENDIF && liMultiples >= 1 RETURN lcColumn ENDFUNC && GetColumnLetter(tiNumber as Integer) as String