vba 在具有最大值的范围内选择单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5655860/
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
Select Cell within a range that has a maximum value
提问by zack
I am trying to select a cell in Excel VBA 2007
我正在尝试在 Excel VBA 2007 中选择一个单元格
Example in row 2, cells A through H have some numbers but cell B2 has the highest value. is there a formula that I could use to get the address of the cell B2 ?
例如在第 2 行，单元格 A 到 H 有一些数字，但单元格 B2 具有最高值。有没有公式可以用来获取单元格 B2 的地址？
Based on this, is there a way I could use a variable to select a Range(":") ?
基于此，有没有办法可以使用变量来选择 Range(":") ？
I am a newbie to VBA so any help would be much appreciated.
我是 VBA 的新手，所以任何帮助将不胜感激。
Thanks
谢谢
回答by Nicola Cossu
=CELL("address",INDEX(A2:H2,MATCH(MAX(A2:H2),A2:H2,0)))
EDIT.
编辑。
Sub max_value_address()
Dim i As Long
i = 2
'This example assigns to A1 cell the address of max value in the range a2:h2
Range("a1").Formula = "=CELL(""Address"",INDEX(A" & i & ":H" & i & ",MATCH(MAX(A" & i & ":H" & i & "),A" & i & ":H" & i & ",0)))"
End Sub
EDIT 2. This version is a little bit more concise.
编辑 2. 这个版本更简洁一点。
Sub max_value_address()
Dim i As Long
Dim str As String
i = 2
str = "a" & i & ":h" & i 'assign to str a2:h2
Range("a1").Formula = "=CELL(""address"",INDEX(" & str & ",MATCH(MAX(" & str & ")," & str & ",0)))"
End Sub
回答by Tiago Cardoso
The below code might help you to reach your goal. Let us know if it's unclear.
以下代码可能会帮助您实现目标。如果不清楚，请告诉我们。
Sub GetHigherValueCellAddress()
Dim oCell As Excel.Range
Dim oRange As Excel.Range
Dim vPrevValue As Variant
Dim sAddress As String
Set oRange = Sheets(1).Range("A1:C2")
For Each oCell In oRange
If oCell.Value > vPrevValue Then
sAddress = oCell.Address
vPrevValue = oCell.Value
End If
Next oCell
MsgBox sAddress
End Sub
