Excel VBA 在特定工作表上查找范围内的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31906571/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA find maximum value in range on specific sheet
提问by user829174
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? Datasheet in this case
如何在特定工作表中搜索？Data在这种情况下的表
Like:
喜欢：
Worksheets(d).Cells(x, 4).Value = Worksheets("Data")... ????? FIND MAX ????
回答by HarveyFrench
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
哈维
回答by vacip
You can pass any valid excel cell reference to the range method as a string.
您可以将任何有效的 excel 单元格引用作为字符串传递给 range 方法。
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)))
回答by Przemyslaw Remin
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.
如果您想快速处理数百万个单元格以找到 MAX/MIN，您需要更重的机器。这段代码会更快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 from here: https://www.mrexcel.com/forum/excel-questions/132404-max-min-vba.html
从这里被盗：https: //www.mrexcel.com/forum/excel-questions/132404-max-min-vba.html
