Excel Get Maximum Value in a Row

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}}

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

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.

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
``````