TheSwamp
Code Red => AutoLISP (Vanilla / Visual) => Topic started by: terrycadd on August 10, 2007, 11:35:41 AM
-
Here's the associated function that references Number2Column.
I want it to default to "A1" if there's a problem.
My version is too limited, as the range only covers "A1" through "ZZ###...".
It should be able to cover any Excel column value, i.e. "ZYXWV..."
If any of you have a similar function or a better resolution please submit it.
Thanks for your assistance.
Terry
; Column_Row - Returns a list of the Column and Row number
; Syntax example: (Column_Row "ZZ999") = '(702 999)
(defun Column_Row (Cell$ / Chr1$ Chr2$ Column# Row#)
(if (< (strlen Cell$) 2)(setq Cell$ "A1"))
(setq Cell$ (strcase Cell$))
(setq Chr1$ (substr Cell$ 1 1))
(setq Chr2$ (substr Cell$ 2 1))
(cond
((and (>= (ascii Chr1$) 65)(<= (ascii Chr1$) 90)(>= (ascii Chr2$) 65)(<= (ascii Chr2$) 90))
(setq Column# (+ (* (- (ascii Chr1$) 64) 26)(- (ascii Chr2$) 64)))
(setq Row# (atoi (substr Cell$ 3)))
);case
((and (>= (ascii Chr1$) 65)(<= (ascii Chr1$) 90))
(setq Column# (- (ascii Chr1$) 64))
(setq Row# (atoi (substr Cell$ 2)))
);case
(t (setq Column# 1 Row# 1))
);cond
;default to "A1" if there's a problem
(if (< Column# 1) (setq Column# 1))
(if (< Row# 1) (setq Row# 1))
(list Column# Row#)
);defun Column_Row
Why am I still a Newt? Can't I be a Bull Frog instead?
-
Something like this ?
With the second routine (alpha2num) I gave in the other thread.
(defun Column-Row (cell / col char row)
(setq col "")
(while (< 64 (ascii (setq char (strcase (substr cell 1 1)))) 91)
(setq col (strcat col char)
cell (substr cell 2)
)
)
(if (and (/= col "") (numberp (setq row (read cell))))
(list (alpha2num col) row)
'(1 1) ;default to "A1" if there's a problem
)
)
(defun alpha2num (str / n)
(if (= 0 (setq n (strlen str)))
0
(+ (* (- (ascii (strcase (substr str 1 1))) 64)
(expt 26 (1- n))
)
(alpha2num (substr str 2))
)
)
)
-
Yes, excellent! :-)
Your version:
Command: (column-row "ZZZ999") = (18278 999) correct!!!
My version:
Command: (column_row "ZZZ999") = (702 1) wrong answer...
-
Why am I still a Newt? Can't I be a Bull Frog instead?
Yes, as soon as you get 201 posts. I'm working on it too.
-
Newts can take several years to mature. If you are prepared to share the music you're listening to and tell jokes and join in political and religious discussions you'll be a croaker before you know it.
.... but that may not help with your understanding of code or CAD.
:-)
qualified:
despite some opinions to the contrary, post count reflects a magnitude of quantity, not necessarily characterising quality .
-
Ok, if I can’t be a Bull Frog, how about a Tadpole instead? (201 posts!) I have a day job programming 8 to 5 with my boss near by. How am I going to achieve 201 posts any time soon? That’s kinda’ high, don’t you think? A hundred maybe… but 201? I can’t just engage in idle chat of three word sentences, just to get up to 201 posts.
I want to be a Bull Frog! (ri’vet! ri’vet!) :whistle:
Terry Cadd
-
qualified:
despite some opinions to the contrary, post count reflects a magnitude of quantity, not necessarily characterising quality .
What Kerry said is that we talk too much but choose to use big words in saying so.