PDA

View Full Version : sum if textbox <>""



ooitzechyi
04-19-2017, 06:13 PM
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
04-19-2017, 06:51 PM
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
04-19-2017, 08:37 PM
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
04-19-2017, 09:32 PM
Hi,
I got it by using On Error Resume Next...
Thanks!