Finding Max of a column in VBA

Question

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

Answer 1

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 

Answer 2

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