output cell in excel

[tsp]

Hi

I am trying to solve an issue in regards to outputting to excel.

I have a list of numbers which need to relate to a column.

Example

1 - column a
2-column b.....

27- column AA

When the number gets over 27 it needs to start to assign to columns AA etc....

cheers

 

[KnutHandsome]

Here's one I prepared earlier...

FUNCTION GetColumnRef RETURNS CHARACTER
  ( input iX as integer) :
/*------------------------------------------------------------------------------
  Purpose:  Returns the Excel column reference for (iX). Used for RANGE.
    Notes:  eg. GetColumnRef(28) returns AB
------------------------------------------------------------------------------*/

  DEF VAR cColumnRef AS CHARACTER NO-UNDO.
  
  DEF var m as integer no-undo.
  def var d as integer no-undo.
  
  IF iX < 1 THEN RETURN ?.

  assign 
    m = (iX - 1) mod 26  /* Modulus */
    d = trunc( ((iX - 1) / 26) , 0). /* Divisor */
  
  if d = 0 then
    cColumnRef = chr( asc("A") + m).
  else
    cColumnRef = chr( asc("A") + d - 1) + chr( asc("A") + m).


  RETURN cColumnRef.


END FUNCTION.

 

[KnutHandsome]

A fine example of looking at your code a long time after you wrote it, and thinking "What lousy comments."

Basically, the 'if d = 0' bit caters for columns with a single letter. The alternative is for double letters (eg. 'CF').

 

[KnutHandsome]

A quick rewrite for efficiency, and hopefully, readability (not comprehensively tested, and with documented limitations; expand at leisure...)

FUNCTION GetColumnRef RETURNS CHARACTER
  ( input iX as integer) :
/*------------------------------------------------------------------------------
  Purpose:  Returns the Excel column reference for (iX). Used for RANGE.
    Notes:  eg. GetColumnRef(28) returns AB
    
            Only caters for 2 letter columns! This max changed in (?) Excel 2007
------------------------------------------------------------------------------*/

  
  DEF var m as integer no-undo.
  def var d as integer no-undo.
  
  IF iX < 1 OR ix > 256 THEN RETURN ?. /* old, wimpy limit = 256 ! new column limit is 16384! */

  IF iX < 27 THEN RETURN chr( asc("A") + m). /* single letter columns */

  /* Use base 26 to work out the alpha 'digits' in the column label */
  assign 
    m = (iX - 1) mod 26  /* Modulus */
    d = trunc( ((iX - 1) / 26) , 0). /* Divisor */

  RETURN chr( asc("A") + d - 1) + chr( asc("A") + m). 


END FUNCTION.

 

[tsp]

brillant cheers for that

will give it a go

thanks again