6

I am trying to find the max value of a cell within A2 to A999. I tried to do a For Loop to loop through each one, but having problems with the logic to find the max rcell. I was hoping there was a built-in rcell.max function?

Set rng = loopset.range("A2-A999")
For Each rcell in rrng.cells
'Find the max rcell
Next rcell
nas
  • 99
  • 1
  • 1
  • 6
  • 5
    why not use the worksheetfunction: `MaxValue = application.worksheetfunction.max(rng)` – Scott Craner Mar 06 '17 at 18:52
  • oh ok; I didn't realize that existed. I'll try that. thx – nas Mar 06 '17 at 18:54
  • So this function works, but is it possible to utilize this to find the value on the same row as the MaxValue for the column on the left. For example, if MaxValue is X, and a column on the left of it has the temperature at the value, and wanted to know, at that same row, what the temperature is i.e., value of the same row to the column to the left. Is this possible? Maybe a different for loop is needed and somehow I need to save the row iteration? – nas Mar 06 '17 at 23:22

2 Answers2

14

Application.worksheetfunction.max(range("a:a")) will do it for you

nwhaught
  • 1,562
  • 1
  • 15
  • 36
4

You can indeed use the function application.worksheetfunction.max(rng) as noted. To give a bit more complete answer, almost ANY Excel formula that's available on the worksheet is available to use in the VBA code window via the application.worksheetfunction collection. This includes max, min, sumif, vlookup, etc.

This should give you the same on-screen description of the arguments involved in the function that you do when using the function on a worksheet. However, as another use noted, using application.max(range) does not give the same argument help. Also as same user noted, there's a difference in error handling between using application.max(rng) and worksheetfunction.max(rng), you can see this in the comment below

As for a programming logic to determine a max value from a list of values, the basic logic is this:

max = 0 'set "inital" max 
For Each rcell in rrng.cells 'loop through values
   if rcell.value > max then 'if a value is larger than the old max, 
   max = rcell.value ' store it as the new max!
Next rcell
Yoav24
  • 316
  • 4
  • 16
  • 1
    This answer is misleading. `Application.WorksheetFunction.Max(...)` is early-bound and you absolutely get parameter tooltips. You're confusing it with the late-bound `Application.Max(...)` version, which works off the extended `Application` interface and, indeed, doesn't give you tooltips - but that's only because it's a late-bound call (resolved at run-time). The most notable difference between the two isn't the tooltips however, it's how they handle errors; early-bound raises a run-time error, late-bound *returns* the error value in the form of a `Variant`. – Mathieu Guindon Mar 06 '17 at 19:54
  • 1
    okay, thanks for improving on my answer. but was it really worth a downvote? Just trying to help – Yoav24 Mar 06 '17 at 21:41
  • Right now your answer contains false information. Downvotes are specifically for telling visitors that *this answer is wrong*, so yes, the downvote is warranted. Let me know once you [edit] it, then I can retract (and possibly reverse) it. – Mathieu Guindon Mar 06 '17 at 21:43
  • So the Application.WorksheetFunction.Max(...) works, but is it possible to utilize this to find the value on the same row as the MaxValue for the column on the left. For example, if MaxValue is X, and a column on the left of it has the temperature at the value, and wanted to know, at that same row, what the temperature is i.e., value of the same row to the column to the left. Is this possible? Maybe a different for loop is needed and somehow I need to save the row iteration? – nas Mar 06 '17 at 23:23
  • 2
    Nas - if you find the _cell adrdress_ of the max value, along with the max value itself (you can use this post: http://stackoverflow.com/questions/5215616/vba-cell-address-of-max), you can then grab the value of the cell to the left of it using a simple OFFSET function ie temperature = maxCell.offset(0, -1) – Yoav24 Mar 06 '17 at 23:38
  • original post nas here - hmm; so the AddressOfMax = WorksheetFunction.Index(rng, WorkSheetFunction.Match(WorksheetFunction.Max(rng),rng,0)).Address works, but when I do the temp= AddressOfMax.Offset(0,-1) it says Run-time error '424': Object Required'. – nas Mar 07 '17 at 23:40
  • 1
    the value you are capturing is a Range object. So, you would need to use "Set temp =" instead of "temp = ". Alternatively, you can use "temp = AddressOfMax.Offset(0,-1).value" to capture the cell's contents directly – Yoav24 Mar 08 '17 at 21:18