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.

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:

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):

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.