Avatar of Wm Allen Smith
Wm Allen Smith

asked on 

VBA Loop with IF/Then, Loop


I have a three column, 40 row  table in excel. Using VBA I need to sum each row of the table(rowTot), then from all of the sums, determine the maximum(rowMax) and minimum (rowMin) value.

I do not need to have the sums, min or max appear anywhere in the, but I do need to call them into a message box.

The code below sums each row and the debug print acts a check for the calculations. I cannot figure out how to determine the minimum and maximum sum values and where to store them.

the code:

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

 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
        Debug.Print I, rowTot
End Sub

Please advise.

Microsoft ExcelVisual Basic ClassicVBA

Avatar of undefined
Last Comment

8/22/2022 - Mon