Re: Scripting in Excel
Re: Scripting in Excel
- Subject: Re: Scripting in Excel
- From: Kai Edwards <email@hidden>
- Date: Sun, 03 Nov 2002 23:13:39 +0000
on Mon, 28 Oct 2002 17:49:52 +0100, Bjorn Van Blanckenberg <email@hidden>
wrote:
>
Thanks the new version works better and faster but want to ask some more
>
questions
>
>
I have changed a bit to work with value that are smaller than 60
>
>
--
>
repeat with colNum from 1 to colTot
>
if rowVal's item colNum is in {"missing", "empty", false} [NO BREAK]
>
or rowVal's item colNum < 60 then set foundList's end [NO BREAK]
>
To "R" & rowNum & "C" & colNum
>
end repeat
>
--
That should do the trick - although you should be aware that an empty cell
will return 0 - and therefore be regarded as matching the above criteria.
One way around this is to check first for the class of 'rowVal's item
colNum'. If the class is <real>[1], then check for a value smaller than 60 -
if not, then move on.
Additional checks like this can also result in repeated evaluations (in this
case, of the value in 'rowVal's item colNum') - which in turn may add a time
penalty. In these situations, it's probably wise to carry out the evaluation
once, and then do the comparisons - something like this:
--
repeat with colNum from 1 to colTot
set val to rowVal's item colNum -- evaluated just once per loop
if val is in {"missing", "empty", false} or [NO BREAK]
(val's class = real and val < 60) then [NO BREAK]
set foundList's end to "R" & rowNum & "C" & colNum
end repeat
--
>
If change the first line to start with the second colomn than
>
Excel colors the wrong cells
Yup. The script was intended to give you an idea of how to approach the
problem - and assumed that the columns would be 1 thru 5. However...
>
It would be nice if the script could start at column 2 thru 5 and skip
>
column 6 and go on with column 7 and skip column 8 and 9 and again go on
>
with column 10 thru 11
>
And if 1 of the value's is found also color the first column
Anything like this is possible - although the more 'rules' you add, the
longer the script is likely to take. For example, specifying non-contiguous
columns inevitably increases the work that the script/Excel has to do. In
addition, the 'pre-comparison' string checks suggested by Paul Berkowitz
(Sun, 27 Oct 2002 08:15:17 -0800) are no longer viable here, because we're
now also checking for numerical values.
Anyway, I've thrown together a routine which I believe does what you're now
asking. Since it's fairly lengthy (sorry!), I'm posting it separately.
I hope that the script - along with the various other contributions to this
thread - have given you a better idea of how you might produce your own
script to achieve your aims.
Best wishes.
Kai
============================
[1] An empty cell will return:
value --> 0
value = 0 --> true
class --> integer
An entered zero, on the other hand, will return:
value --> 0.0
value = 0 --> true
class --> real
--
email@hidden
email@hidden
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.