Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I found that the following code is getting max value in range:

Cells(Count, 4)=Application.WorksheetFunction.Max(Range(Cells(m, 1),Cells(n, 1)))
How can I search within a specific sheet? Data sheet in this case
Like: 
Worksheets(d).Cells(x, 4).Value = Worksheets("Data")... ????? FIND MAX ????
This will often work, but is missing a reference:
 worksheets("Data").Cells(Count, 4)=  Application.WorksheetFunction.Max _
    ( worksheets("Data").range( cells(m,1) ,cells(n,1) )
The text "cells" should be preceded by a reference to which worksheet the cells are on, I would write this:
worksheets("Data").Cells(Count, 4) = Application.WorksheetFunction.Max _
    ( worksheets("Data").range( worksheets("Data").cells(m,1) ,worksheets("Data").cells(n,1) )
This can also be written like this which is clearer:
with worksheets("Data")
    .Cells(Count, 4) =  Application.WorksheetFunction.Max _
                            ( .range( .cells(m,1) ,.cells(n,1) )
End With 
I hope this helps.
Harvey
                I like the one with the With statement, makes it a lot more clear, and also makes it faster. Note though that you shoudln't double-specify the worksheet! I mean, don't specify it before every range method, as that is not only useless, but it may lead to errors. This is enough: Application.WorksheetFunction.Max  (range( .cells(m,1) ,.cells(n,1) )
– vacip
                Aug 9, 2015 at 17:51
                It will only lead to an error if you try specifying a different worksheet for the outer .range object. (I know, why would you... But still...) Like I said, I'll dig around, and maybe post a question about this. Seems interesting.
– vacip
                Aug 9, 2015 at 18:00
                @Vacip in that case getting an error is very useful, as it will help you identify a problem that might otherwise go unnoticed... By fully qualifying the range you are defining what you are expecting the parameters to be (ie cells on the sheet preceeding the range object).  If cells are defined by variables then putting the sheet object in front of the range will help stop unforeseen conditions NOT raising an error.
– HarveyFrench
                Aug 9, 2015 at 18:39
You can pass any valid excel cell reference to the range method as a string.
Application.WorksheetFunction.Max(range("Data!A1:A7"))
In your case though, use it like this, defining the two cells at the edges of your range:
Application.WorksheetFunction.Max _
    (range(worksheets("Data").cells(m,1),worksheets("Data").cells(n,1)))
                range needs a reference to which worksheet the cells it refers to are going to be on.... see my answer for more info
– HarveyFrench
                Aug 9, 2015 at 17:29
                This is not entirely true. The result of the range method is a collection of cells. That collection is not independent of the worksheet they are on, so you don't need to specify the worksheet again.
– vacip
                Aug 9, 2015 at 17:43
                Yes you're right, that had not occurred to me before.  However, it doesn't hurt to put it in and make code very readable and clear.  I'm now wondering whether you can create a range object with areas on many sheets. I would say no...do you agree.
– HarveyFrench
                Aug 9, 2015 at 17:52
                I stand corected, range is an object, not a method. Something is weird here. This is an interesting problem, I might just post a question about this. I can't rellay put my finger on it, but something feels off with this problem.
– vacip
                Aug 9, 2015 at 17:58
                MySheet.range(..)  is a method of the sheet object that returns a range object.  The range object returned is instantiated (created) to contain references to cells that already exist, the cells it references depend on the parameters that were used.    You can think of the range object itself containing a big collection of objects references of type cell the refer to cells that already exist.  OO can be really confusing at times...  MyRange.Parent is a property of the range object that returns a reference to a sheet object that exists already, and is the  sheet on which the range can be found.
– HarveyFrench
                Aug 9, 2015 at 18:44
If you want to process quickly milion of cells to find MAX/MIN, you need heavier machinery. This code is faster then Application.WorksheetFunction.Max.
Function Max(ParamArray values() As Variant) As Variant
   Dim maxValue, Value As Variant
   maxValue = values(0)
   For Each Value In values
       If Value > maxValue Then maxValue = Value
   Max = maxValue
End Function
Function Min(ParamArray values() As Variant) As Variant
   Dim minValue, Value As Variant
   minValue = values(0)
   For Each Value In values
       If Value < minValue Then minValue = Value
   Min = minValue
End Function
Stolen from here: https://www.mrexcel.com/forum/excel-questions/132404-max-min-vba.html
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.