Tuesday, September 29, 2020

#1 2020-09-13 06:27:07 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Excel Find Command not working

I  may be asking the impossible but I have a Mac book pro and an iMac both running High Sierra  and AppleScript 2.10. The following script works OK on the iMac but I get an error trying to run it on my MacBook.I have no idea where to start but thought it worth asking in case anyone else has had the same problem.

This is the error message
error "Microsoft Excel got an error: range \"AA3:AE3\" doesn’t understand the “find” message." number -1708 from range "AA3:AE3"


And this the code.

Applescript:


tell application "applications:Microsoft office 2011:Microsoft Excel.app"
   tell active sheet to set lastrow to (first row index of (get end (last cell of column 1) direction toward the top))
   repeat with i from 3 to lastrow
       set maxcol to evaluate name "=MAX(AA" & i & ":AE" & i & ")"
       set TargetMax to find (range ("AA" & i & ":AE" & i)) what maxcol look at whole with match case
       set color index of interior object of TargetMax to 40
   end repeat
end tell

Thanks for any suggestions

Peter

Offline

 

#2 2020-09-14 11:54:05 am

roosterboy
Member
Registered: 2010-04-19
Posts: 26

Re: Excel Find Command not working

Instead of using find, you could try something like this:

Applescript:


tell application "Microsoft Excel"
   
   tell active sheet
       
       set lastrow to (first row index of (get end (last cell of column 1) direction toward the top))
       
       repeat with i from 3 to lastrow
           
           set rng to "AA" & i & ":AE" & i
           try
               -- e.g. =MATCH(MAX(AA3:AE3),AA3:AE3,0)
               set maxcell to (evaluate name "=MATCH(MAX(" & rng & ")," & rng & ", 0)") as integer
               set c to cell maxcell of range (rng)
               set color index of interior object of c to 40
           end try
           
       end repeat
       
   end tell
   
end tell

It uses MAX to get the maximum value in the row, then MATCH to find the first cell that contains that value, then sets the color of that cell.

(I wrapped those lines in a try block just to be safe, though it really shouldn't be necessary, as you are matching on a value that you know for certain is there. Better safe than sorry, though.)

Caveat: This will only color in the first cell with the max value in a row; if more than one cell has the same value, subsequent cells won't be colored. But then, your code has the same limitation so maybe that's not an issue.

Note that I tried like heck to get conditional formatting to work but gave up after too many failed attempts. That would have made this so much easier.

Offline

 

#3 2020-09-14 12:29:43 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Re: Excel Find Command not working

Once again many thanks I will try that and I understand the limitation of values that are the same it was more of a visual guide.

After my post I continued to work on the issue and the 1st thing I realized that the numbers on my MacBook Pro where percentages and not pn the iMac. Thought that may have solved the issue but also the actual data is the result of scraping dat form the web and the number of decimal place varies, so that may be an issue.

Anyway thanks again I will now try your suggestion as if it works on percentages it will eliminate a step

Thank you

Peter

Offline

 

#4 2020-09-14 12:57:01 pm

KniazidisR
Member
Registered: 2019-03-03
Posts: 1311

Re: Excel Find Command not working

I have not Office 2011 on my Mac. Try 3 things:

1) add search by rows (do not change the order of find statement parameters
2) remove with match case (because the value is some number (it is decimal number), so no case needed)
3) wrap the find statement with try block (to solve no matches founded):

Applescript:

set TargetMax to find (range ("AA" & i & ":AE" & i)) search order by rows what maxcol

Last edited by KniazidisR (2020-09-14 01:11:31 pm)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#5 2020-09-14 03:07:21 pm

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Re: Excel Find Command not working

RoosterBoy

Worked perfectly thank you very much.

KniazidisR, thanks for your suggestion but I get error -1708 in the try block. Just in case I miss understood your suggestions I have posted the script below.

Applescript:


tell application "applications:Microsoft office 2011:Microsoft Excel.app"
   tell active sheet to set lastrow to (first row index of (get end (last cell of column 1) direction toward the top))
   repeat with i from 3 to lastrow
       set maxcol to evaluate name "=MAX(AA" & i & ":AE" & i & ")"
       try
           set TargetMax to find (range ("AA" & i & ":AE" & i)) search order by rows what maxcol
           set color index of interior object of TargetMax to 40
       end try
   end repeat
end tell

Offline

 

#6 2020-09-14 09:01:01 pm

KniazidisR
Member
Registered: 2019-03-03
Posts: 1311

Re: Excel Find Command not working

I don't know why you use repeat loop this way. No need at all. So, I wrote your code in other form and it works fine with my Microsoft Excel for Mac (version 16.16.24 (200713):

Applescript:


tell application "Microsoft Excel" to tell active sheet
   
   set lastrow to (first row index of (get end (last cell of column 1) direction toward the top))
   
   set maxcol to evaluate name "=MAX(A1:B" & lastrow & ")"
   
   try
       set TargetMax to find (range ("A1:B" & lastrow)) search order by rows what maxcol
       set color index of interior object of TargetMax to 40
   on error
       display dialog "No matches founded"
   end try
   
end tell

return TargetMax

Last edited by KniazidisR (2020-09-14 09:16:39 pm)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#7 2020-09-15 02:00:52 am

roosterboy
Member
Registered: 2010-04-19
Posts: 26

Re: Excel Find Command not working

That will color in the max value in the range A1:Bx (where x = lastrow)

But what the original code does is highlight the max value in each row. That's what the loop is for.

Offline

 

#8 2020-09-15 02:26:46 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 1311

Re: Excel Find Command not working

roosterboy wrote:

That will color in the max value in the range A1:Bx (where x = lastrow)

But what the original code does is highlight the max value in each row. That's what the loop is for.



Yes, I see it now. But the find command still works great. Here is the final code:

Applescript:


tell application "Microsoft Excel" to tell active sheet
   
   set lastrow to (first row index of (get end (last cell of column 1) direction toward the top))
   
   repeat with i from 3 to lastrow
       set maxcol to evaluate name "=MAX(AA" & i & ":AE" & i & ")"
       try
           set TargetMax to find (range ("AA" & i & ":AE" & i)) search order by rows what maxcol
           set color index of interior object of TargetMax to 40
       on error
           display dialog "No matches founded"
       end try
   end repeat
   
end tell

NOTE: The difference from original OP's code is 2: I tell to active sheet to find range and not to application "Excel" itself, I use try block to resolve no matches founded

Last edited by KniazidisR (2020-09-15 11:27:59 am)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 13.1
Ram: 4 GB

Offline

 

#9 2020-09-15 10:55:34 am

MitchBVI
Member
Registered: 2006-02-14
Posts: 197

Re: Excel Find Command not working

Thank you very much will try that.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)