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: bill <email@hidden>
- Date: Mon, 28 Oct 2002 19:07:53 +0800
>
The OP referred to a specific number of columns, suggesting that these
would
>
remain constant. However, in this type of situation, there's still a chance
>
that the number of rows containing the data will vary. If so, to avoid the
>
risk of skipping rows in a longer column (or of needlessly checking empty
>
ones in a shorter one), it might be useful to include some form of
automatic
>
'last-row' adjustment.
Kai,
My last suggestion is based on one of my script, with minor adjustment.
Surely, some form of automatic last row adjustment is better than fixed
range.
>
From the tests above, it became clear that the main bottlenecks in both
>
cases were associated with frequent calls to Excel - firstly to get each
>
cell's value (or to find each relevant cell), and then to format each
>
'found' cell.
Maybe there9re two many apple events between applescript & Excel, so the
speed is... Slow. Well, would you mind to test this one? :)
-- script starts
tell application "Microsoft Excel"
set {a, b} to {Row of first Cell of UsedRange of ActiveSheet, Column of
first Cell of UsedRange of ActiveSheet}
set rangeV to Value of UsedRange of ActiveSheet
end tell
set {c, d} to {count of rangeV, count of item 1 of rangeV}
-- compose list of cell reference
set cellRef to {}
repeat with i from a to (a + c - 1)
repeat with j from b to (b + d - 1)
set end of my cellRef to "R" & i & "C" & j
end repeat
end repeat
-- flatten the nested list (rangeV) to a one dimension list
set cellV to {}
repeat with m in my rangeV
repeat with n from 1 to count of m
set end of my cellV to item n of m
end repeat
end repeat
-- do the comparison, if match, add the cell ref to ocList
set ocList to {}
repeat with q from 1 to count of cellV
if item q of my cellV is in {"missing", "FALSE", false, "empty"} then
set end of ocList to item q of my cellRef
end repeat
-- change the color & bold of relevant cell
tell application "Microsoft Excel"
repeat with r in ocList
set {ColorIndex of Font of Cell r, Bold of Font of Cell r} to {3.0,
true}
end repeat
end tell
-- script ends
Brief explanation:
1. get the first cell9s row & column in number of the UsedRange. (a form of
automatic first row adjustment) If we assume the first cell is R1C1, skip
it :)
2. get the value of the UsedRange, the result is a nested list.
These two steps involve Excel.
3. compose the list of cell reference (R1C1, R1C2,... Etc.)
4. Flatten the list of value into a one dimension list, then, the list of
cell reference is corresponded to this list.
5. Do the comparison, if match, add the cell reference to the ocList.
These three steps using applescript only.
6. Loop thru the ocList, which contains cell references that the value is
matched, change the color & bold of fonts.
This step involve Excel again.
bill
_______________________________________________
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.