I’m currently writing a script to do a find in Excel but have come across a small problem.
I’ve looked at a number of previous posts to this forum to try and find an answer but as yet nothing.
If I run the code below all works fine.
tell application "Microsoft Excel"
activate
set thisSearchTerm to "110928"
set searchRange to range ("A:A")
set theResult to find searchRange what thisSearchTerm
if theResult is not nothing then
set foundRow to (first row index of theResult)
set value of range ("B" & foundRow) to "Y"
end if
end tell
The moment I try and set the search term to an item in a list, or the value from another cell in Excel, I get the following error:-
I’m baffled as I’ve scripted Excel, to do a find, on quite a few occasions now and all has worked fine. This one has me baffled.
Should the search term be a string or text? Or, is there something wrong with the search itself? The script errors on this line:-
set theResult to find searchRange what thisSearchTerm
Please can someone point me in the right direction.
Thanks for the help.
That’s one of the things I’d tried, passing the value into the find as text, strings etc.
If I set my search term like this:-
set searchTerm to "401468"
… all works fine.
The moment I try to pick up a value from a cell and use that as the search criteria the search fails. Here are a few variations i’ve tried:-
set searchTerm to (formula of range ("C" & i) of worksheet "Sheet1" of workbook "thisWorkBook.xls")
set searchTerm to (formula of row 1 of column 3) as text
copy value of cell "C1" to searchTerm
I’ve tried variations of these themes, for example the way I pick up the value/formula from the cell, to changing the searchTerm to a string or text.
So far I’m stumped as to why it fails.
I can’t help with Excel specifically, but I notice that your first script makes no mention of the range’s location (eg. ‘worksheet “Sheet1” of workbook “thisWorkBook.xls”’). Might that be a contributory factor? Similarly with the rows and columns.
I made this to work for me, but I did 2 modifications. First of all, I removed the parenthesis from the range specifier, and I opened a saved workbook, it is worth to mention that I got Excel 2008.
set thefile to POSIX path of ((path to desktop as text) & "Arbeidsbok3.xlsx")
tell application "Microsoft Excel"
activate
open thefile
set thisSearchTerm to "3"
set searchRange to range "A:A" of active sheet
set theResult to find searchRange search order by rows what thisSearchTerm
if theResult is not nothing then
set foundRow to (first row index of theResult)
set value of range ("B" & foundRow) to "Y"
end if
end tell
Nigel, I tried your suggestions but still the same result.
The problem seems to be with the search term is specified.
If I use:
set thisSearchTerm to "110928"
…the find works.
However, when I try to pull the value in from an existing cell, like this:-
set thisSearchTerm to (formula of range ("C1") of sheet "Sheet1" of workbook "comparison3.xls")
…it fails with the error message I listed in my first post. I’ve even tried putting all the criteria in to a list but that still fails?
In the end I pulled all the data out of Excel, into some lists, and used your Binary Search routine (thanks for that :)) to interrogate the data. It’s a workround I used in the end which worked, I’m still trying to figure out why the ‘find’ fails. It looks like Excel is picky when it comes to how the search criteria is specified.
McUsrII, thanks for the help. I’m using Excel 2004. Your script worked for me too, however if I try and specify the search criteria in a different way it fails?
There is a full scripting reference for Microsoft Excel 2004 out there, not that I think it would help much in this case.
Maybe you should try to coerce the formula to text, ( . as text) when you assign it to the search term, and maybe also log the new variable, and see that it looks like text?
Thanks for your post. Yeah, I’ve got the Excel Applescript Reference and I had a look in there.
I’ve also tried coercing the value/formula to text when pulling the search criteria from the required cell.
Beginning to wonder if Excel is really fussy about the format of the cell.
Having dug a little deeper on this one I think I may have found the answer.
It looks like when scripting a ‘find’, in Excel, the ‘find’ needs a ‘try’ round it as below.
try
set theResult to find searchRange what searchTerm
on error
set theResult to ""
end try
If the search doesn’t find anything it looks like it throws an error instead of returning nothing.
