Count textbox values greater than 0

A

agfac

New Member
Joined
Dec 16, 2013
Messages
20
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

Code: 
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
it is the "If" line, it was probably highlighted in red when you entered it

Code: 
Sub test()
    
    Dim i As Integer
    Dim divisor As Integer
    Dim soma As Long
    Dim n As Double
    
    soma = 0
    divisor = 0
    
    For i = 1 To 11
        
        n = TextBox("TextBox" & i).Value
        soma = soma + n                          ' add the values as you loop through
        
        If n > 0 Then
            divisor = dividor + 1
        End If
        
    Next i
    
    Label203.Caption = soma \ divisor
    
End Sub
 
Upvote 0
It doesn't work.

It gives the error sub ou function not defined and pointed this line:

Code: 
n = TextBox("TextBox" & i).Value
 
Upvote 0
i am assuming that the text boxes are ActiveX textboxes on a worksheet


Code: 
Sub test()
    
    Dim i As Integer
    Dim divisor As Integer
    Dim soma As Long
    Dim n As Double
    
    soma = 0
    divisor = 0
    
    For i = 1 To 11
        
        n = Sheets("Sheet1").Shapes("TextBox" & i).OLEFormat.Object.Object.Value
        
        soma = soma + n                          ' add the values as you loop through
        
        If n > 0 Then
            divisor = dividor + 1
        End If
        
    Next i
    
    Label203.Caption = soma \ divisor
    
End Sub
 
Upvote 0
The Value of a text box is a string. You many have to use the Val function to convert that to a number.
 
Upvote 0
mikerickson said:
The Value of a text box is a string. You many have to use the Val function to convert that to a number.
Click to expand...

I did that but it gave me an overflow error and pointed to resultado.

I pointed the mouse on soma and it gave "soma=0" and on divisor and it gave "divisor=0"

Code: 
Dim i As Integer
    Dim divisor As Integer
    Dim soma As Double
    Dim n As Double
    Dim resultado As Double
    
    soma = 0
    divisor = 0
    
    For i = 1 To 11
        
        n = Me.Controls(Val("TextBox" & i)).Value
        soma = soma + n
        
        If n > 0 Then
            divisor = divisor + 1
        End If
        
    Next i
    
    resultado = soma / divisor
    
    Label203.Caption = Format(resultado, "#,##0")
 
Upvote 0
See if this works (assumes the TextBoxes on a Userform)

Code: 
Private Sub CommandButton1_Click()
    Dim ctl As Control, divisor As Long, soma As Double, resultado As Double
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" 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.
 
Upvote 0
Marcelo Branco said:
See if this works (assumes the TextBoxes on a Userform)

Code: 
Private Sub CommandButton1_Click()
    Dim ctl As Control, divisor As Long, soma As Double, resultado As Double
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" 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.
Click to expand...
It didn't give the overflow error but the label caption is always 1.

Textbox values are for example 1,1 or 1,2 or 1,3, so the label caption (or "resulatdo") must be 1,1 or 1,2 or 1,3 as this is an average. But it is always 1.
 
Upvote 0
agfac said:
It didn't give the overflow error but the label caption is always 1.

Textbox values are for example 1,1 or 1,2 or 1,3, so the label caption (or "resulatdo") must be 1,1 or 1,2 or 1,3 as this is an average. But it is always 1.
Click to expand...

The format is wrong.

Change
Me.Label203.Caption = Format(resultado, "#,##0")

to

Me.Label203.Caption = Format(resultado, "#.##0")

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
188
Patriot2879
P
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
429
dmt32
D
I
  • Solved
Clling a module process
2
Replies
11
Views
356
ipbr21054
I
E
  • Solved
Userform as search tool
Replies
5
Views
261
Akuini
Akuini
D
  • Solved
TextBox looping help required in VBA
Replies
3
Views
387
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,064
Messages
6,122,936
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top