I am currently using the code below to “find” all instances of a particular cell value by running a repeat loop through all the cells. Great, that works. However, when there are over 10,000+ cells in the column, it gets pretty slow. I am trying to find all instances of a particular cell value in column A. I see the ‘find’ and ‘find next’ commands from Excel’s script dictionary, but I want to be able to find ‘all’. Any ideas on how to speed this up with ‘find’/‘find next’ (or some other command)?
on find_cells_with(column_letter, search_term)
tell application "Microsoft Excel"
tell active sheet
set last_row_num to first row index of ¬
(get end (cell 1 of row (count rows)) direction toward the top)
set the_range to column_letter & "1:" & column_letter & last_row_num
set first_foundcell to search_term
set found_cell_list to {}
repeat with x from 1 to count of (every cell of range the_range)
if value of (cell x of (range (the_range))) = first_foundcell then
set found_cell_list's end to (get address of (cell x of (range (the_range))) ¬
without row absolute and column absolute)
end if
end repeat
end tell
return found_cell_list
end tell
end find_cells_with
tell application "Microsoft Excel"
set searchTerm to "baker"
set searchRange to get range "a:a" of active sheet
set oneFoundCell to get resize (get cells of row -1 of searchRange) row size 1 column size 1
set firstAddress to ""
set foundCells to {}
try
set oneFoundCell to (find searchRange what searchTerm after oneFoundCell look in values look at whole search order by columns search direction search next without match case)
set firstAddress to get address oneFoundCell
end try
if firstAddress = "" then
return "no matching cells"
else
repeat until ((get address oneFoundCell) = firstAddress) and (0 < (count of foundCells))
copy oneFoundCell to end of foundCells
set oneFoundCell to find next searchRange after oneFoundCell
end repeat
return get address of foundCells without row absolute and column absolute
end if
end tell
Thanks for the reply. When I ran your script (I’m running XL 2008 12.2.4 on 10.6.3), I got an error on the “get resize” line:
tell application "Microsoft Excel"
get range "A:A" of active sheet
--> range "A:A" of active sheet
get every cell of last row of range "A:A" of active sheet
--> {}
get resize {} row size 1 column size 1
--> error number -1708
Result:
error "Microsoft Excel got an error: {} doesn't understand the get resize message." number -1708 from {}
I didn’t use that syntax so I’m not sure what is going on.
But I did think of a non looping aproach
tell application "Microsoft Excel"
set searchTerm to "able"
set searchRange to range "A:A"
autofilter range searchRange field 1 criteria1 "=" & searchTerm operator autofilter and
set foundCells to (get special cells searchRange type cell type visible)
return get address of foundCells without row absolute and column absolute
end tell
Hey this looks like it works, it returns a single range with about 30 cell adresses - is there a way to get the individual cell addresses from the “special cells” range result?
Actually I figured it out - your other code for using ‘find/find next’ had an accidental “-1” instead of “1” (Where the get resize command is) When I modifed that, it gave me the result that I wanted, a list of each cell address. Thanks!!
