VBA Excel - COUNT, AVG, & MIN with textboxes

**patrickmt** · 05-13-2013, 03:07 PM

I have 25 text field boxes. Each one is labled as such: txt1, txt2, txt3, txt4, etc. What I need to do is have it loop though and find the AVG and MIN of all text boxes while also excluding any zeros and being able to count how many non zeros are entered.
For example...

txt1 - 456
txt2 - 455
txt3 - 456
txt4 - 0
txt5 - 449
txt6 - 445

The average would be 452.2 and the minimal number would be 445. Altough 6 boxes have been filled in, only 5 boxes have non "0" in them so therefore the COUNT fould be 5. Only these three numbers would theen be submitted to my excel sheet.

I know how to do all this via excel but it would be so much easier (in my own thinking) to just allow this form do all the work. There will be 3 different people using this form a day and 2 of them hate and dispise of excel, so that was the reason behind all of this.

Everything is already set up and ready to submit, I just can't figure the looping process out.

**tehneXus** · 05-13-2013, 05:18 PM

try this:

Option Explicit

Private Sub CommandButton1_Click()
    Dim ctrl As Control
    Dim sum As Long, avg As Double, count As Long, min As Long, max As Long
    min = 2147483647
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" And Left(ctrl.Name, 3) = "txt" Then
            If IsNumeric(ctrl.Value) Then
                If ctrl.Value > 0 Then
                    count = count + 1
                    sum = sum + ctrl.Value
                    If max < ctrl.Value Then max = ctrl.Value
                    If min > ctrl.Value Then min = ctrl.Value
                End If
            End If
        End If
    Next ctrl
    If count = 0 Then MsgBox "Count = 0": Exit Sub
    avg = sum / count
    MsgBox "Sum = " & sum & vbLf & _
           "Count = " & count & vbLf & _
           "Avg = " & avg & vbLf & _
           "Min = " & min & vbLf & _
           "Max = " & max
End Sub

**patrickmt** · 05-14-2013, 01:21 PM

Thanks so much for your help on getting this resolved for me.

It works great, except, I need the three numbers - count, avg, & min - to be submitted to a spreadsheet not a msgbox. Should I create another control button and call it "calculate" and then have the data sent to another text field which then have the submitt button? Which would be the easiest to do?

**mehmetcik** · 05-14-2013, 01:41 PM

Hi

TehneXus has given you three variables avg, min, max.

if you want these on a spread sheet then delete the section of code that opens the msgbox and write the variables to the spreadsheet

Sheets("Output").select
range("A1").value = avg
range("A2").value = min
range("A3").value = max

**tehneXus** · 05-14-2013, 01:43 PM

hi,

replace the msgbox with

    With ThisWorkbook.Worksheets("Sheet1")
        .Range(.Cells(2, 1), .Cells(2, 3)).Value2 = Array(count, avg, min)
    End With

it will write the values into sheet1, row2, A:C.

Regards

**patrickmt** · 05-14-2013, 02:00 PM

Why would I be getting an error with my code?

Private Sub cmdSubmit_Click()
    Dim ctrl As Control
    Dim sum As Long, avg As Double, count As Long, min As Long, max As Long
    min = 2147483647
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" And Left(ctrl.Name, 3) = "cfs" Then
            If IsNumeric(ctrl.Value) Then
                If ctrl.Value > 0 Then
                    count = count + 1
                    sum = sum + ctrl.Value
                    If max < ctrl.Value Then max = ctrl.Value
                    If min > ctrl.Value Then min = ctrl.Value
                End If
            End If
        End If
    Next ctrl
    If count = 0 Then MsgBox "There was no river flow entered. Please check the Wastewater Effluent Report and try again.": Exit Sub
    avg = sum / count
    MsgBox "Flow Hours = " & count & vbLf & _
           "Average River Flow = " & avg & vbLf & _
           "Minimal River Flow = " & min
    With ThisWorkbook.Worksheets("collection")
        lRow = ws.Cells(33, 3).End(xlUp).Row + 1
    With ws
        .Cells(lRow, "AR").Value = avg
        .Cells(lRow, "AS").Value = min
        .Cells(lRow, "AT").Value = count
        .Cells(lRow, "AU").Value = Me.txtTotalizer.Value
    ClearAll
     End With
     End With
End Sub

**tehneXus** · 05-14-2013, 02:12 PM

you haven't set anything to 'ws'
try

Private Sub cmdSubmit_Click()
    Dim ctrl As Control
    Dim sum As Long, avg As Double, count As Long, min As Long, max As Long
    min = 2147483647
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" And Left(ctrl.Name, 3) = "cfs" Then
            If IsNumeric(ctrl.Value) Then
                If ctrl.Value > 0 Then
                    count = count + 1
                    sum = sum + ctrl.Value
                    If max < ctrl.Value Then max = ctrl.Value
                    If min > ctrl.Value Then min = ctrl.Value
                End If
            End If
        End If
    Next ctrl
    If count = 0 Then MsgBox "There was no river flow entered. Please check the Wastewater Effluent Report and try again.": Exit Sub
    avg = sum / count
    MsgBox "Flow Hours = " & count & vbLf & _
           "Average River Flow = " & avg & vbLf & _
           "Minimal River Flow = " & min
    With ThisWorkbook.Worksheets("collection")
        lRow = .Cells(33, 3).End(xlUp).Row + 1
        .Cells(lRow, "AR").Value = avg
        .Cells(lRow, "AS").Value = min
        .Cells(lRow, "AT").Value = count
        .Cells(lRow, "AU").Value = Me.txtTotalizer.Value
    'ClearAll
     End With
End Sub