CODEWITHSUNDEEP
vbaexcel
user9932378
user9932378

Reputation:

How to Use MAX with .FIND? in VBA (Excel)

I have been working on this issue for a while now and cannot seem to get it to work. I have the following VBA code and I want to get the MAX value of the Resize for range1. I have tried putting MAX at the beginning, the end, and everywhere else I could think of.

This is what works now without the 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

This is my latest attempt to modify the code above to include 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

Upvotes: 0

Views: 42

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Something like this:

    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

Upvotes: 2

Related Questions