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.
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
Instead of using find, you could try something like this:
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.
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
I have not Office 2011 on my Mac. Try 3 things:
- add search by rows (do not change the order of find statement parameters
- remove with match case (because the value is some number (it is decimal number), so no case needed)
- wrap the find statement with try block (to solve no matches founded):
set TargetMax to find (range ("AA" & i & ":AE" & i)) search order by rows what maxcol
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.
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
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):
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
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:
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
Thank you very much will try that.