Excel VBA find the maximum value in a range on a specific sheet
I found that the following code gets the maximum value in the range:
Cells(Count, 4)=Application.WorksheetFunction.Max(Range(Cells(m, 1),Cells(n, 1)))
How can I search within a specific sheet? Data
in this case
Like:
Worksheets(d).Cells(x, 4).Value = Worksheets("Data")... ????? FIND MAX ????
source to share
This often works, but the link is missing:
worksheets("Data").Cells(Count, 4)= Application.WorksheetFunction.Max _
( worksheets("Data").range( cells(m,1) ,cells(n,1) )
In the text of "cells" there must be a reference to the sheet on which the cells are located, I would write the following:
worksheets("Data").Cells(Count, 4) = Application.WorksheetFunction.Max _
( worksheets("Data").range( worksheets("Data").cells(m,1) ,worksheets("Data").cells(n,1) )
It can also be written like this:
with worksheets("Data")
.Cells(Count, 4) = Application.WorksheetFunction.Max _
( .range( .cells(m,1) ,.cells(n,1) )
End With
Hope this helps.
Harvey
source to share
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, use it like this, defining two cells at the edges of your range:
Application.WorksheetFunction.Max _
(range(worksheets("Data").cells(m,1),worksheets("Data").cells(n,1)))
source to share
If you want to quickly process millions of cells to find MAX / MIN, you will need heavier equipment. This code is faster than 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
Next
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
Next
Min = minValue
End Function
Stolen here: https://www.mrexcel.com/forum/excel-questions/132404-max-min-vba.html
source to share