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: Kai Edwards <email@hidden>
- Date: Sun, 27 Oct 2002 15:12:47 +0000
on Thu, 24 Oct 2002 16:46:23 +0800, bill <email@hidden> wrote:
>
> I want to search in 5 columns the word "missing" or "FALSE" or "empty" and
>
> if it is found color it red and set the font to bold
>
>
Assume that you already know the range to search these words...
That's fine if the space that your data occupies never changes - or if the
user is willing and able to modify the script manually before each run.
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.
>
...this approach is to compose a list of each cell name (e.g. R1C1, R1C2,...);
>
then loop through each cell and do comparison.
Out of curiosity, I carried out some speed comparisons between the
'cell-by-cell' analysis referred to above and the 'find-in-Excel' approach
that I suggested around the same time.
I had originally assumed that the routine's aims would be to identify
certain cells (i.e. those containing the values "missing", "FALSE" or
"empty") as exceptions. In other words, my suggestion was based on the
premise that cells meeting the comparison criteria would represent a fairly
small proportion of the total range.
While it may have been a reasonable assumption, the 'cell-by-cell' method
prompted me to consider cases in which the incidence of 'found' cells might
be higher.
My tests, involving the analysis of 500 cells (100 rows x 5 columns),
confirmed that both approaches have advantages and disadvantages - depending
on the ratio of 'found' to total cells.
Naturally, both methods take longer as the number of total and 'found' cells
increase.
However, with a relatively low number of total cells and a high proportion
of 'found' cells, the cell-by-cell method came out on top. When 50% of total
cells met the search criteria, the method was over 40% faster than using
Excel's find. When _all_ cells satisfied the search criteria, cell-by-cell
was 85% faster than the alternative.
Conversely, as the found-to-total cell ratio fell, 'find-in-Excel' came into
its own:
------------- ----------------
proportion of 'find-in-Excel'
found cells speed difference
------------- ----------------
25% + 3%
20% + 16%
15% + 45%
10% + 99%
5% + 255%
0% + 5425%
-------------------------------
Since (IMO) both methods were still a bit too slow for comfort, I decided to
try a slightly different approach.
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.
It seemed the key to better performance would be to perform these
time-critical operations in bulk - rather than individually.
To avoid message length difficulties, I'll send separately some details of
my efforts to achieve this - together with a sample script and some further
performance comparisons.
Kai
--
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.