Userform [Averages of Textboxes IF populated)

[Grizlore]

I am trying to create some VBA code, which will average the contents of up to twelve textboxes on a Userform, and then put the average in the next one.

I have sorted this, HOWEVER.. I have a couple of problems.

• 
  The average isn’t correct

• Some of the textboxes may be blank, and therefore should not be included in the calculation

Here is my code

Private Sub CommandButton3_Click()
    Dim results(12) As Double
    Dim ave As Double
    results(1) = CDbl(TextBox5.Value)
    results(2) = CDbl(TextBox6.Value)
    results(3) = CDbl(TextBox7.Value)
    results(4) = CDbl(TextBox8.Value)
    results(5) = CDbl(TextBox9.Value)
    results(6) = CDbl(TextBox10.Value)
    results(7) = CDbl(TextBox11.Value)
    results(8) = CDbl(TextBox12.Value)
    results(9) = CDbl(TextBox13.Value)
    results(10) = CDbl(TextBox14Value)
    results(11) = CDbl(TextBox15.Value)
    results(12) = CDbl(TextBox16Value)

    ave = Application.WorksheetFunction.Average(results)

    TextBox17.Value = ave

End Sub

Could someone please point me in the right direction please?

Many Thanks

 

[rowlystravel]

i think it might be easier to use a formula for this, and maybe create a button to run it

I am afraid i can't help with the VBA but i would guess a forumla would be far easier

 

[Stephen_Tsui]

Try below code

Private Sub CommandButton2_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
    If TypeName(Me.Controls(i)) = "TextBox" Then
        If Me.Controls(i).Value <> "" Then
            a = a + Val(Me.Controls(i).Value)
            b = b + 1
        End If
    End If
Next i

TextBox13.Value = a / b

End Sub

 

[Stephen_Tsui]

Private Sub CommandButton1_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
If TypeName(Me.Controls(i)) = "TextBox" Then

If Me.Controls(i).Value <> "" Then
a = a + Val(Me.Controls(i).Value)
b = b + 1
End If
End If
Next i

TextBox13.Value = a / b

End Sub

 

[Grizlore]

Thanks for you help, however...

The calculation is wrong. There are other TextBoxes on the Userform, and I guess they are being included too. [?]

Is there a way around this ?


Thanks for you help!

 

[Stephen_Tsui]

Sorry Plz try below code

Private Sub CommandButton1_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
     if Me.Controls(i).name  = "TextBox5" or Me.Controls(i).name  = "TextBox6" or Me.Controls(i).name  = "TextBox7" or Me.Controls(i).name  = "TextBox8" or Me.Controls(i).name  = "TextBox9" or Me.Controls(i).name  = "TextBox10" or Me.Controls(i).name  = "TextBox11" or Me.Controls(i).name  = "TextBox12" or Me.Controls(i).name  = "TextBox13" or Me.Controls(i).name  = "TextBox14" or Me.Controls(i).name  = "TextBox15" or Me.Controls(i).name  = "TextBox16" Then
        If Me.Controls(i).Value <> "" Then
            a = a + Val(Me.Controls(i).Value)
            b = b + 1
        End If
    End If
Next i

TextBox13.Value = a / b

End Sub

 

[Grizlore]

Thanks again

I get an error here TextBox17.Value = a / b (I have changed your "TextBox13.Value")

I think the "a" and "b" comes back as a zero

This is the code i am using.

Private Sub CommandButton3_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
     If Me.Controls(i).Name = "Textbox5" Or Me.Controls(i).Name = "Textbox6" Or Me.Controls(i).Name = "Textbox7" Or Me.Controls(i).Name = "Textbox8" Or Me.Controls(i).Name = "Textbox9" Or Me.Controls(i).Name = "Textbox10" Or Me.Controls(i).Name = "Textbox11" Or Me.Controls(i).Name = "Textbox12" Or Me.Controls(i).Name = "Textbox13" Or Me.Controls(i).Name = "Textbox14" Or Me.Controls(i).Name = "Textbox15" Or Me.Controls(i).Name = "Textbox16" Then
        If Me.Controls(i).Value <> "" Then
            a = a + Val(Me.Controls(i).Value)
            b = b + 1
        End If
    End If
Next i

TextBox17.Value = a / b

End Sub

 

[Stephen_Tsui]

Try this

Private Sub CommandButton3_Click()

Dim a As Double
Dim b As Integer

a = 0
b = 0

For i = 0 To Me.Controls.Count - 1
     If Me.Controls(i).Name = "TextBox5" Or Me.Controls(i).Name = "TextBox6" Or Me.Controls(i).Name = "TextBox7" Or Me.Controls(i).Name = "TextBox8" Or Me.Controls(i).Name = "TextBox9" Or Me.Controls(i).Name = "TextBox10" Or Me.Controls(i).Name = "TextBox11" Or Me.Controls(i).Name = "TextBox12" Or Me.Controls(i).Name = "TextBox13" Or Me.Controls(i).Name = "TextBox14" Or Me.Controls(i).Name = "TextBox15" Or Me.Controls(i).Name = "TextBox16" Then
        If Me.Controls(i).Value <> "" Then
            a = a + Val(Me.Controls(i).Value)
            b = b + 1
        End If
    End If
Next i

if b = 0 then 
     msgbox "Plz entry things!!"
     exit sub
end if

TextBox17.Value = a / b

End Sub

 

[Stephen_Tsui]

Change all Textbox to TextBox, then may fix.

 

[Grizlore]

thanks for your time....

"b" always seems to be 0, and therefore I keep getting the MsgBox

?