Show the largest date and the smallest date on the listbox Userform

[sofas]

Hello, I have this code to calculate the sum of certain columns on the Listbox and show the result on the Textbox I now need to get the largest and smallest date on the Listbox column 2 and show them on TEXTBOX 7 and TEXTBOX8
Format "mm/dd/yyyy"

Sub SUM_COL()

Dim Cont As Long
sum = 0: sum1 = 0: sum2 = 0: sum3 = 0: sum4 = 0: sum5 = 0: sum6 = 0: sum7 = 0
Cont = 0
 Set F = Sheets("data")
        With ListBox1
            For r = 0 To .ListCount - 1
             Cont = Cont + 1
                sum = sum + .List(r, 5)
                sum1 = sum1 + .List(r, 6)
                sum2 = sum2 + .List(r, 10)
                sum3 = sum3 + .List(r, 11)
                sum4 = sum4 + .List(r, 12)
                sum5 = sum5 + .List(r, 19)
                
            'MIN DATE
          sum6 = Application.WorksheetFunction.Min(.List(r, 2))
          
            'MAX DATE
          sum7 = Application.WorksheetFunction.Max(.List(r, 2))
            
            Next r
        End With

Me.TextBox1.Value = sum
Me.TextBox2.Value = sum1
Me.TextBox3.Value = sum2
Me.TextBox4.Value = sum3
Me.TextBox5.Value = sum4
Me.TextBox6.Value = sum5
Me.TextBox7.Value = sum6
Me.TextBox8.Value = sum7
Me.TextBox9.Value = Cont
End Sub

 

[frabulator]

With such a small amount, would it not be just as quick to test/compare inside the loop?

If .List(r, 2) > sum7 Then sum7 = .List(r, 2)
If .List(r, 2) < sum6 Then sum6 = .List(r, 2)
MsgBox (Format(sum6, "MM/DD/YYYY") & " - " Format(sum7, "MM/DD/YYYY"))

(above not tested)

 

[Domenic]

Actually, you can avoid looping altogether...

    With Application
        Sum = .Sum(.Index(Me.ListBox1.List, 0, 6)) 'sixth column
        sum1 = .Sum(.Index(Me.ListBox1.List, 0, 7)) 'seventh column
        'etc
        '
        '
        sum6 = .Min(.Index(Me.ListBox1.List, 0, 2)) 'second column
        sum7 = .Max(.Index(Me.ListBox1.List, 0, 2)) 'second column
    End With

Hope this helps!

 

[sofas]

Thank you, I will try again. And back again. I missed mentioning that the dates on the listbox are random and not arranged, for example, from smallest to largest. Because after the experiment I only get the largest date. Does this cause the code to not work well?

 

[frabulator]

Thank you, I will try again. And back again. I missed mentioning that the dates on the listbox are random and not arranged, for example, from smallest to largest. Because after the experiment I only get the largest date. Does this cause the code to not work well?

Both solutions would result in the same output.
While mine utilized the loop you already have in place, I think that @Domenic 's response is actually the 'correct' way to go about programming it.
Clean, with no loops.

But, like I said, both work. Doesn't really matter how you get there, right? Just that you get there

​

 

[Domenic]

I wouldn't say "correct", maybe more efficient, depending on the size of the data.