Excel Get Maximum Value in a Row

Scripting Forums AppleScript | Mac OS X
#1

I have a script which returns the max value in a column but cannot figure out how to make it work on a row.


tell application "Microsoft Excel"
	tell active sheet
		set ColValues to value of (cell ("D2:D11"))
		set MaxValue to item 1 of ColValues
		repeat with x in ColValues
			if x > MaxValue then set MaxValue to x
		end repeat
		display dialog MaxValue
	end tell
end tell

If I change the ColValues variable to “E1:N1” , a row with the same numbers it does not work.
In the scrip using columns ColValues returns each number in curly brackets

{{45.0}, {66.5}, {52.5}, {95.0}, {30.0}, {94.0}, {70.0}, {55.0}, {35.0}, {85.0}}

Using it on rows separates the values with commas not individual brackets so I assume this is the issue but do not know how to fix.

{{45.0, 66.5, 52.5, 95.0, 30.0, 94.0, 70.0, 55.0, 35.0, 85.0}}

#2

This is because of the way Excel represents cells in AppleScript. A group of cells is represented as a nested list of rows and columns, such that {{R1C1, R1C2}, {R2C1, R2C2}, {R3C1, R2C2}, etc…}

So a single row of multiple columns comes out like this: {{R1C1, R1C2, R1C3, etc…}}

And a single column of multiple rows like this: {{R1C1}, {R2C1}, {R3C1}, etc…}

You could loop through the nested lists, but an easier way to do it is just to use Excel’s built-in formula:

tell application "Microsoft Excel"
	
	set maxCol to evaluate name "=MAX(E1:N1)"
	set maxRow to evaluate name "=MAX(D2:D11)"
	
end tell
1 Like
Correct Ranking Results but output different to actual results
#3

Thanks you very much I had no idea you could use excel formulas . I do have a question if I may actually two can you use all Excel formulas this way and in actual operation i will have to determine what the column address are so if i use say get address, can i use that in the =Max formula? On second thoughts I should try that rather than ask you. Thank you again, I had by the way just finished building a routine to create the list from entries in a row.

#6

Obviously, roosterboy’s solution is good but since I’d noticed this while looking at another post earlier, I thought I’d belatedly flesh the thread out with a repeat version for both columns and rows. I made them both a bit more complicated than necessary so I could include the cell and range in the dialogues as well as activate the min and max cell.

For columns…

use scripting additions
tell application "Microsoft Excel"
	activate
	set colRge to range "D2:D11"
	set outerList to value of colRge
	set maxValue to item 1 of outerList
	repeat with yy from 1 to count of cells in colRge
		if item 1 of item yy of outerList > maxValue then
			set maxValue to item 1 of item yy of outerList
			set maxCell to get address cell yy of colRge without row absolute and column absolute
		end if
	end repeat
	set xr to get address colRge without row absolute and column absolute
	activate object cell maxCell
	display dialog "Max value: " & maxValue & " in cell " & maxCell & " of range " & xr
end tell

And for rows…

use scripting additions
tell application "Microsoft Excel"
	activate
	set rowRge to range "E1:N1"
	set innerList to item 1 of (get value of rowRge)
	set minValue to item 1 of innerList
	repeat with xx from 1 to count of cells of rowRge
		if item xx of innerList < minValue then
			set minValue to contents of item xx of innerList
			set minCell to get address cell xx of rowRge without row absolute and column absolute
		end if
	end repeat
	set xr to get address range "E1:N1" without row absolute and column absolute
	activate object cell minCell
	display dialog "Min value: " & minValue & " in cell " & minCell & " of range " & xr
end tell