sum if textbox <>""

[ooitzechyi]

Hey guys,
Is there any way to simplify the code for below request?
I have textbox1 to textbox10 AND textbox1A to textbox10A
and I would like to have
labelsum.caption = (textbox1.value/textbox1A.value) + (textbox2.value/textbox2A.value) + (textbox3.value/textbox3A.value)+ (textbox4.value/textbox4A.value) + ... +(textbox10.value/textbox10A.value)

This has no problem if all the textbox are fill up with number.
But what if there are some textbox left empty?

I know I can do in the way that declare If textbox1 <>'' then... for all textbox...
but it's too troublesome...
Is there any way to simplify this?

Thanks~

[Leith Ross]

Hello ooitzechyi,

This should work provided all the controls are on a UserForm.

Sub TestA()


    Dim ErrMsg  As String
    Dim j       As Variant
    Dim k       As Variant
    Dim n       As Integer
    Dim Total   As Double
        
        ErrMsg = " does not contain a valid value." & vbLf _
                & "Please enter a number and try again."
        
        For n = 1 To 10
            j = Me.Controls("TextBox" & n).Value
            If j = "" Or (Not IsNumeric(j)) Then MsgBox "TextBox" & n & ErrMsg: Exit Sub
            
            k = Me.Controls("TextBox" & n & "A").Value
            If k = "" Or (Not IsNumeric(k)) Then MsgBox "TextBox" & n & "A" & ErrMsg: Exit Sub
            
            Total = Total + (j / k)
        Next n
        
        LabelSum.Caption = Total
        
End Sub

[ooitzechyi]

Hi Leith Ross,
Thanks! it does help but one problem is, it request to fill up those empty textbox but not skip...
I try to change to GoTo... but prompted error "Type Mismatch"...

Dim ErrMsg  As String
    Dim j       As Variant
    Dim k       As Variant
    Dim n       As Integer
    Dim Total   As Double
     
     
    For n = 1 To 5
        j = UserForm1.Controls("TBTime" & n).Value
        If j = "" Or (Not IsNumeric(j)) Then GoTo Line1
         
        k = UserForm1.Controls("TBBatch" & n).Value
        If k = "" Or (Not IsNumeric(k)) Then GoTo Line1
Line1:
        Total = Total + (j / k)
         
    Next n

    LabelTtlLoss.Caption = Total

[ooitzechyi]

Hi,
I got it by using On Error Resume Next...
Thanks!