vba Max()的VBA单元地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5215616/
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
VBA Cell address of Max()
提问by lamba
I have something like
我有类似的东西
sdMax = WorksheetFunction.Max(Range("D2", Cells(emptyRow, 4)))
to find the maximum number of column D
找出列 D 的最大数目
How do I find the location of this maximum number?
我如何找到这个最大数字的位置？
回答by chris neilsen
Defined as a user defined function in vba, returning the address as a string
在vba中定义为用户自定义函数，以字符串形式返回地址
Function AddressOfMax(rng As Range) As String
AddressOfMax = WorksheetFunction.Index(rng, WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0)).Address
End Function
Or returning a range reference
或者返回一个范围引用
Function AddressOfMax(rng As Range) As Range
Set AddressOfMax = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0))
End Function
these functions assume rng is one column wide
这些函数假设 rng 是一列宽
These functions can be used in the sheet
eg
这些功能可以在工作表中使用，
例如
=AddressOfMax(C:C)
or in vba
eg
或在 vba
例如
Dim r As Range
Set r = AddressOfMax(Range("D2", Cells(emptyRow, 4)))
