Re: Scripting in Excel -> search different words in column and color them
Re: Scripting in Excel -> search different words in column and color them
- Subject: Re: Scripting in Excel -> search different words in column and color them
- From: Paul Berkowitz <email@hidden>
- Date: Sun, 27 Oct 2002 08:15:17 -0800
On 10/27/02 7:18 AM, "Kai Edwards" <email@hidden> wrote:
>
The following variation starts with a single call to Excel - to get the
>
values of _all_ cells in the specified range.
>
>
Then, entirely in AS, a cell reference list compiled (but comprised of only
>
those cells that match the comparison criteria).
>
>
Finally, Excel is called to carry out the formatting operation - again in
>
just one* hit.
<snip>
>
So it was only when there were no cells (or a negligible number of them) to
>
be found, that this approach lagged a little behind 'find-in-Excel'. (And
>
since fewer found cells mean shorter delays generally, I'm not too unhappy
>
about that.)
I think you might be able to make it even quicker by first coercing the
entire list of lists to string (use tab as delimiter), checking to see if
the resulting string contains any of the values you are seeking. if it
doesn't, end of script. if it does, check each rowVal: if the row doesn't
contain any of your search values, move on without checking colItems. If a
rowVal _does_ contain one of the search values, only then do it item by
item. At least for the "no cells" scenario, this should be much faster; I'm
not sure about the row-by-row checking, but it's worth a try. You could keep
just the valString check if the rowVal check doesn't help.
tell application "Microsoft Excel"
set valList to Range
("R1C1:R" & ActiveSheet's UsedRange's last Row's Row & "C" & 5)'s Value
end tell
-- new lines:
set valString to valList as string
if valString does not contain "missing" and valString does not contain
"empty" and valString does not contain "false" then return
--
set {foundList, rowTot, colTot} to {{}, count valList, count valList's item
1}
repeat with rowNum from 1 to rowTot
set rowVal to valList's item rowNum
-- new 'if':
if rowVal contains {"missing"} or rowVal contains {"empty"} or rowVal
contains {false} then
repeat with colNum from 1 to colTot
if rowVal's item colNum is in {"missing", "empty", false} then
set foundList's end to "R" & rowNum & "C" & colNum
end repeat
end if
end repeat
if foundList = {} then return
set {tid, text item delimiters} to {text item delimiters, ","}
set {foundStr, text item delimiters} to {foundList as string, tid}
repeat with theChunk in chunk(foundStr)
tell application "Microsoft Excel" to set {Range theChunk's Font's Bold,
Range theChunk's Font's ColorIndex} to {true, 3}
end repeat
--
Paul Berkowitz
_______________________________________________
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.