Hey, I have a question I couldn’t find the answer to, and I’m very curious to understand…
I sometimes use scripts to search big excel tables (usually derived from MS Access) and recently I’ve started running into strange problems.
To be very short, my script (that actually works btw) sometimes returns an error message saying “a:a” range doesn’t understand the find message, and when right after that you switch over to the sheet and hit apple button + f (the window that pops up still has the appropriate number it serached for set) and hit enter excel brings up the appropriate value… so in other words, the number is there but excel is reluctant to find it… the weird thing is this stuff can also happen when I get some numbers I need from a pivot table, but when I get filemaker to import all the data and then export it to an excel sheet and then copy those over to the appropriate columns then the find works again… and sometimes it does anyway and sometimes it doesn’t. We store numbers as “text” and I don’t know if that has anything to do with it, because if I set it to number or general format it still doesn’t change wether or not it can search, if I copy the format from one to another and … well anyway it doesn’t make a difference… I can try to copy into a word document and insert it back as text it doesn’t work, the only thing that makes a difference is if I have the filemaker do an import export, but that’s alot of work… can anyone help me?
Here is the script btw:
tell application "Microsoft Excel"
activate
activate object worksheet "Sheet1"
set theRange to (get value of range "A2" of active sheet)
if theRange ≠("") then
set theEnd to get end range "A1" direction toward the bottom
get address theEnd
set i to first row index of theEnd
repeat with i from i to 2 by -1
set theDouble to (get value of cell i of column 1 of active sheet)
activate object worksheet "all contracts"
set gc to (find (range "a:a") what theDouble)
set fc to get first row index of gc
set theRange to get value of cell fc of column 2 of active sheet
activate object worksheet "Sheet1"
set the formula of row i of column 3 to theRange
end repeat
end if
end tell