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
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
