I had need to calculate column references for an Excel spreadsheet for
columns greater than 26. You know, so you can come up with the reference
like: A1:AG1.
Fun stuff.
Anyway, the scheme for this is deceptive. A -> Z is easy. But A # 0 or 1
strictly. It's tricky. I suspect this is one reason there are functions in
Excel specifically for doing this sort of thing.
However, we have to write our own. In the event someone else needs this
particular bit of code here is a solution I came up with. It could be used
in other contexts I suppose. You can set the base to something smaller. Try
3, for example.
====================================
$chars:="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
$number:=2000
$base:=26
$reference:=""
If ($number=0) // there is no zero
$reference:=""
Else
While ($number>0)
$remainder:=$number%$base
$number:=$number\$base
If ($remainder=0) // borrow base from number
$remainder:=$base
$number:=$number-1
End if
$reference:=$chars[[$remainder]]+$reference
If ($number>0) & ($number<=$base)
$reference:=$chars[[$number]]+$reference
$number:=0
End if
End while
End if
ALERT($reference)
=================
Posting this in the hope all this nerdy time isn't completely wasted.
--
Kirk Brooks
San Francisco, CA
=======================
*We go vote - they go home*
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**********************************************************************
