Question

Excel 求助：VBA求满足条件的最大值怎么写

Answer 1

Sub maxx()
Dim i As Integer, max As Currency
max = Cells(1, 2)
For i = 1 To 20
If Cells(i, 1) > max Then
max = Cells(i, 1).Value
End If
Next
MsgBox "最大值是" & max
End Sub

楼上的建立字典来查询，把简单的问题搞复杂了。而且占用内存，运行效率会下降。

Answer 2

例：求A列中小于1500的最大值，用Application.Evaluate 方法，调用Excel数组函数：

Sub max()
Dim max_if
 max_if = Application.Evaluate("MAX(if((A:A<1500),A:A))")
End Sub

Answer 3

Sub lqxs()
Dim Arr, i&, d
Set d = CreateObject("Scripting.Dictionary")
Sheet1.Activate
Arr = [a1].CurrentRegion
For i = 2 To UBound(Arr)
If Not d.exists(Arr(i, 2)) Then
d(Arr(i, 2)) = Arr(i, 3)
Else
If d(Arr(i, 2)) < Arr(i, 3) Then d(Arr(i, 2)) = Arr(i, 3)
End If
Next
[f2].Resize(d.Count, 1) = Application.Transpose(d.keys)
[g2].Resize(d.Count, 1) = Application.Transpose(d.items)
End Sub