如何将 MAX 与 一起使用.找到?在VBA(Excel)中

Question

我已经在这个问题上工作了一段时间，但似乎无法让它工作。我有以下VBA代码，我想获取范围1的调整大小的最大值。我尝试过将MAX放在开头，结尾以及我能想到的其他任何地方。

这是现在没有MAX的工作：

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BHAInfo"  And ws.Name <> "BHADetail" Then
Set rng1 = ws.Cells.Find(What:="Total Length").Resize(15)
Set rng2 = Sheets("BHAInfo").Cells(Rows.Count, "J").End(xlUp).Offset(1)
Rng.Value = rng1.Value
End If
Next

这是我修改上述代码以包含MAX的最新尝试：

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BHAInfo" And ws.Name <> "BHADetail" Then
Set rng1 = ws.Cells.Find.FormuaArray = "=MAX(What:= ""Total Length"")).Resize(15)"
Set rng2 = Sheets("BHAInfo").Cells(Rows.Count, "J").End(xlUp).Offset(1)
rng2.Value = rng1.Value
End If
Next

Answer 1

像这样：

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BHAInfo" And ws.Name <> "BHADetail" Then
Set rng1 = ws.Cells.Find(What:="Total Length")
If Not rng1 Is Nothing Then
Sheets("BHAInfo").Cells(Rows.Count, "J").End(xlUp).Offset(1).Value = _
Application.Max(rng1.Resize(15,1))
End If
End If
Next