Count textbox values greater than 0

[agfac]

Hi,

I wonder to count textbox values greater than 0 and I made this code but it's not working.

Could someone help me please?

Thanks

Dim i As Integer
Dim divisor As Integer

    divisor = 0
    
    For i = 1 To 11
        If ("TextBox" & i).Value > 0 Then
            divisor = dividor + 1
        End If
    Next i


Dim soma As Long
    
    soma = TextBox1.Value + TextBox2.Value + TextBox3.Value + TextBox4.Value + TextBox5.Value + TextBox6.Value + TextBox7.Value + TextBox8.Value + TextBox9.Value + TextBox10.Value + TextBox11.Value
    
    Label203.Caption = soma \ divisor

 

[Marcelo Branco]

It works!!

Thank you very much

You are welcome. Glad for helping

M.

 

[agfac]

Hi,

As I said it works but I wonder it works only for textbox 1 to 11 and not for all.

How could I do that?

Thanks

 

[Marcelo Branco]

Hi,

As I said it works but I wonder it works only for textbox 1 to 11 and not for all.

How could I do that?

Thanks

Sorry, I'm not following you. Do you want to add only TextBox1 to TextBox11?
Are there others textboxes?

M.

 

[agfac]

Sorry, I'm not following you. Do you want to add only TextBox1 to TextBox11?
Are there others textboxes?

M.

Yes, there are more textboxes.

But I want this code to work only for textbox 1 to 11. I want that it calculates the average only for those values (textbox1.value to textbox11.value)

Thanks

 

[Marcelo Branco]

Yes, there are more textboxes.

But I want this code to work only for textbox 1 to 11. I want that it calculates the average only for those values (textbox1.value to textbox11.value)

Thanks

I'm very busy now. Just a suggestion: you can use ctl.Name property.

Something like
If ctl.Name = "TextBox1" OR ctl.Name="TextBox2" OR ..... Then
'Do your stuff
End If

M.

 

[agfac]

I'm very busy now. Just a suggestion: you can use ctl.Name property.

Something like
If ctl.Name = "TextBox1" OR ctl.Name="TextBox2" OR ..... Then
'Do your stuff
End If

M.

I tried this but it gives a type mismatch error.

Dim ctl As Control, divisor As Long, soma As Double, resultado As Double
        
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox1" Or "Textbox2" Or "Textbox2" Or "Textbox3" Or "Textbox4" Or "Textbox5" Or "Textbox6" Or "Textbox7" Or "Textbox8" Or "Textbox9" Or "Textbox10" Or "Textbox11" Then
        If IsNumeric(ctl.Object.Value) And ctl.Object.Value > 0 Then
                divisor = divisor + 1
                soma = soma + ctl.Object.Value
            End If
        End If
    Next ctl
    
    resultado = soma / divisor
    
    Me.Label203.Caption = Format(resultado, "#.##0")

 

[Marcelo Branco]

Do not use If TypeName(ctl) = "TestBox1" Or TypeName(ctl)="TextBox2" OR....

Try

If ctl.Name = "TestBox1" OR ctl.Name = "TextBox2" OR ctl.Name = "TextBox3" OR ...........Then

Something like

Private Sub CommandButton1_Click()
    Dim ctl As Control, divisor As Long, soma As Double, resultado As Double

    For Each ctl In Me.Controls
        If ctl.Name = "TextBox1" Or ctl.Name = "TextBox2" Or ctl.Name = "TextBox3" _
            Or ctl.Name = "TextBox4" Or ctl.Name = "TextBox5" Or ctl.Name = "TextBox6" _
            Or ctl.Name = "TextBox7" Or ctl.Name = "TextBox8" Or ctl.Name = "TextBox9" _
            Or ctl.Name = "TextBox10" Or ctl.Name = "TextBox11" Then
        
            If IsNumeric(ctl.Object.Value) And ctl.Object.Value > 0 Then
                divisor = divisor + 1
                soma = soma + ctl.Object.Value
            End If
            
        End If
    Next ctl
    
    resultado = soma / divisor
    
    Me.Label203.Caption = Format(resultado, "#.##0")
End Sub

M.

 

[agfac]

It works but now I've another problem

The reason I wanted to select the textboxes that's because now I want to repeat the code to another textboxes to calculate them average.

I tested that but it stopped working. Now it's not calculation the first average nor the second average.

Dim ctl As Control, divisor As Long, soma As Double, resultado As Double        
    
    For Each ctl In Me.Controls
        If ctl.Name = "TextBox1" Or ctl.Name = "Textbox2" Or ctl.Name = "Textbox3" Or ctl.Name = "Textbox4" Or ctl.Name = "Textbox5" Or ctl.Name = "Textbox6" Or ctl.Name = "Textbox7" Or ctl.Name = "Textbox8" Or ctl.Name = "Textbox9" Or ctl.Name = "Textbox10" Or ctl.Name = "Textbox11" Then
        If IsNumeric(ctl.Object.Value) And ctl.Object.Value > 0 Then
                divisor = divisor + 1
                soma = soma + ctl.Object.Value
            End If
        End If
    Next ctl
    
    resultado = soma / divisor
    
    With Label308
        .Caption = Format(resultado, "#.##0")
        .Font.Bold = True
    End With


    For Each ctl In Me.Controls
        If ctl.Name = "TextBox23" Or ctl.Name = "Textbox32" Or ctl.Name = "Textbox25" Then
        If IsNumeric(ctl.Object.Value) And ctl.Object.Value > 0 Then
                divisor = divisor + 1
                soma = soma + ctl.Object.Value
            End If
        End If
    Next ctl
    
    resultado = soma / divisor
    
    With Label309
        .Caption = Format(resultado, "#.##0")
        .Font.Bold = True
    End With

 

[Marcelo Branco]

Be careful with the names of the textboxes. For example, if the real name is "TextBox1" (without the quotes) don't use "Textbox1"

Also, is necessary to reset the variables soma and divisor before the second loop (see in red)

See if this works. Worked for me

Private Sub CommandButton1_Click()
    Dim ctl As Control, divisor As Long, soma As Double, resultado As Double
    
    For Each ctl In Me.Controls
        If ctl.Name = "TextBox1" Or ctl.Name = "TextBox2" Or ctl.Name = "TextBox3" Or _
            ctl.Name = "TextBox4" Or ctl.Name = "TextBox5" Or ctl.Name = "TextBox6" Or _
            ctl.Name = "TextBox7" Or ctl.Name = "TextBox8" Or ctl.Name = "TextBox9" Or _
            ctl.Name = "TextBox10" Or ctl.Name = "TextBox11" Then
            If IsNumeric(ctl.Object.Value) And ctl.Object.Value > 0 Then
                divisor = divisor + 1
                soma = soma + ctl.Object.Value
            End If
        End If
    Next ctl
    
    If divisor > 0 Then
        resultado = soma / divisor
        With Me.Label308
            .Caption = Format(resultado, "#.##0")
            .Font.Bold = True
        End With
    End If
    
    [COLOR=#ff0000]divisor = 0
    soma = 0
[/COLOR]
    For Each ctl In Me.Controls
        If ctl.Name = "TextBox23" Or ctl.Name = "TextBox32" Or ctl.Name = "TextBox25" Then
            If IsNumeric(ctl.Object.Value) And ctl.Object.Value > 0 Then
                divisor = divisor + 1
                soma = soma + ctl.Object.Value
            End If
        End If
    Next ctl
        
    If divisor > 0 Then
        resultado = soma / divisor
        With Me.Label309
            .Caption = Format(resultado, "#.##0")
            .Font.Bold = True
        End With
    End If
End Sub

M.

 

[agfac]

Finally solved!

Thanks a lot!