VBA Loop with IF/Then, Loop

Perhaps?
OM Gang

Dim I As Integer
 Dim r As Integer
 Dim dataRng As Range
 Dim a1 As Range
 Dim rowTot As Integer
 Dim rowMax As Integer
 Dim rowMin As Integer
  
  For I = 0 To 40
  
  counter = 0
  

     With Range("a1")

          Range(.Offset(I, 0), .Offset(I, 2)).Select
          rowTot = .Application.WorksheetFunction.Sum(Range(.Offset(I, 0), .Offset(I, 2)))
      
           End With
         If I = 0 Then
               rowMax = rowTot
               rowMin = rowTot
         Else
               If rowTot > rowMax Then rowMax = rowTot
               If rowTot < rowMin Then rowMin = rowTot
         End If
         Debug.Print I, rowTot, & "   Max = " & rowMax & "    Min = " & rowMin
     Next
     
 End Sub

Select allOpen in new window

Thank you both for solutions. I was not sure how to apply the if/then.
 :)

Clarification:  My intMin = 32000 is arbitrary, but close to the integer type maximum of 32,768.  You could use any significantly large number there (ex., if your data can't produce values higher than 10000, you could use that instead).

-Glenn

Please find below the code. The max and min of the sum column get stored in cell H2 and H3.

You can change the range if you want to place it somewhere else.

Sub testRow()




Dim I As Integer
Dim r As Integer
Dim dataRng As Range
Dim a1 As Range
Dim rowTot As Integer
Dim rowMax As Integer
Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
 
 For I = 0 To 20
 
 counter = 0
 

    With Range("a1")

         Range(.Offset(I, 0), .Offset(I, 2)).Select
         rowTot = .Application.WorksheetFunction.Sum(Range(.Offset(I, 0), .Offset(I, 2)))
     
          End With
          Next
'        Debug.Print I, rowTot, rowMax, counter
        Range("H2").Formula = Application.WorksheetFunction.Max(Range("E2:E" & lrow))
        Range("H3").Formula = Application.WorksheetFunction.Min(Range("E2:E" & lrow))
    
    
End Sub

Select allOpen in new window

Curious as to your selection of solution.  They are basically the same.  In Glenn Ray's solution, what happens all values are less than zero?
Did you even try my solution?

OM Gang

Hi OMGang,

You raise a great point--I actually find your solution more useful especially if the min sum that is returned  results in  a negative number.

Thanks :D

Okay, good point.  Here's the tweaked code to initialize Min and Max on the first values.

Sub testRow()
    Dim r As Integer
    Dim dataRng As Range
    Dim intTot, intMax, intMin As Integer
    For I = 1 To 21
        With Range("A1")
            intTot = Application.WorksheetFunction.Sum(Range(.Offset(I, 0), .Offset(I, 2)))
            If I = 1 Then
               intMin = intTot
               intMax = intTot
            Else
                If intTot < intMin Then intMin = intTot
                If intTot > intMax Then intMax = intTot
            End If
        End With
        Debug.Print r, intTot, intMax, intMin
    Next
End Sub

Select allOpen in new window

Hi guys,
Thank you for your responses. After running all of your example found that none returned a minimum .
40 records. All of the code sets identified the  correct maximum sum, but each code set example returned  a minimum sum of zero.

Hereis the updated code:
Dim I As Integer
 Dim r As Integer
 Dim dataRng As Range
 Dim a1 As Range
 Dim rowTot As Long
 Dim rowMax As Long
 Dim rowMin As Long
 
  For I = 0 To 40
 
  Counter = 0
 

     With Range("a1")

'
        Range(.Offset(I, 0), .Offset(I, 2)).Select
          rowTot = .Application.WorksheetFunction.Sum(Range(.Offset(I, 0), .Offset(I, 2)))
     
           End With
         If I = 0 Then
               rowMax = rowTot
               rowMin = rowTot
         Else
               If rowTot > rowMax Then rowMax = rowTot
               If rowTot < rowMin Then rowMin = rowTot
         End If
       
     Next
       MsgBox rowTot & vbNewLine & rowMax & vbNewLine & rowMin
 End Sub


I have attached the data set.. Ignore the content from column E and to the right; that is just to check the calculations.

Thanks!
Excel_MAC_Workbook1Test.xlsm

I'm looking in the file you provided.  I see code in Module1 but it doesn't have any of our changes in it.  Am I missing something?
OM Gang

That's because you are iterating past the existing data and finding the sum of empty rows...hence a minimum of zero.

Here is updated code that will only check the contiguous range of data, starting in row 1.

Sub testrows()
    Dim r, intLastRow As Integer
    Dim lngTot, lngMax, lngMin As Long
  
    intLastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row - 1
    
    For r = 0 To intLastRow
        With Range("A1")
            Range(.Offset(r, 0), .Offset(r, 2)).Select
            lngTot = .Application.WorksheetFunction.Sum(Range(.Offset(r, 0), .Offset(r, 2)))
        End With
        If r = 0 Then
            intMax = lngTot
            intMin = lngTot
        Else
            If lngTot > intMax Then intMax = lngTot
            If lngTot < intMin Then intMin = lngTot
        End If
    Next r
    MsgBox lngTot & vbNewLine & "Max: " & intMax & vbNewLine & "Min: " & intMin
End Sub

Select allOpen in new window

I also suggest using standard prefixes like "int" for Integer variable and "lng" for Long variable, with some occasional exceptions (like "r" for a variable linked to rows).

-Glenn
EE_Excel_MAC_Workbook1Test.xlsm

Concur with Glenn Ray; your procedure as you wrote it doesn't check to see if the cells contains data which is why it ends up with 0 for rowMIN once it iterates past the last row.  Here's the output from my procedure added to your excel file.

   Max = 60    Min = 60
   Max = 95    Min = 60
   Max = 130    Min = 60
   Max = 165    Min = 60
   Max = 200    Min = 60
   Max = 235    Min = 60
   Max = 270    Min = 60
   Max = 305    Min = 60
   Max = 340    Min = 60
   Max = 375    Min = 60
   Max = 410    Min = 60
   Max = 445    Min = 60
   Max = 480    Min = 60
   Max = 515    Min = 60
   Max = 550    Min = 60
   Max = 585    Min = 60
   Max = 620    Min = 60
   Max = 655    Min = 60
   Max = 690    Min = 60
   Max = 690    Min = 60
   Max = 690    Min = 60
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0
   Max = 690    Min = 0