Running Count - Sum not summing when inputting less than expected quantity-CodePudding

I am using a form that takes an item and quantity. I'm trying to create a running count that consists of the quantity (denoted as qtytxt1, qtytxt2, etc) of each item. Each item has its own quantity input field denoted with the ending number (i.e., qtytxt1 applies to item 1).

I am trying to have a cell located in (emptyRow, 27) to output the sum of the total quantity of all items inputted into the form (i.e., Item 1 qty = 2,000; Item 2 qty = 3,000; Expected Output = 5,000).

There are a total of 10 input fields for "Item/Qty," however, not all 10 fields are expected to be used. I have created a code that seems to work as I prefer, however, I would receive a sum error message when entering less than 10 items.

Dim emptyRow As Long
Dim data As Worksheet
Dim runCount As Long
        
Worksheets("mining history").Activate
emptyRow = WorksheetFunction.CountA(Range("i:i"))   6
Set data = Sheets("data")
runCount = 0

If qtytxt2.Value = "" Then
    qtytxt2.Value = 0
    
    ElseIf qtytxt3.Value = "" Then
    qtytxt3.Value = 0
    
    ElseIf qtytxt4.Value = "" Then
    qtytxt4.Value = 0
    
    ElseIf qtytxt5.Value = "" Then
    qtytxt5.Value = 0
    
    ElseIf qtytxt6.Value = "" Then
    qtytxt6.Value = 0
    
    ElseIf qtytxt7.Value = "" Then
    qtytxt7.Value = 0
    
    ElseIf qtytxt8.Value = "" Then
    qtytxt8.Value = 0
    
    ElseIf qtytxt9.Value = "" Then
    qtytxt9.Value = 0
    
    ElseIf qtytxt10.Value = "" Then
    qtytxt10.Value = 0
    
End If

If IsEmpty(Range("E:E")) Then
    'Is Empty
    runCount = 0
    Else
    ' Not Empty
    runCount = WorksheetFunction.Sum(qtytxt1.Value, qtytxt2.Value, qtytxt3.Value, qtytxt4.Value, qtytxt5.Value, qtytxt6.Value, qtytxt7.Value, qtytxt8.Value)
        
    Cells(emptyRow, 27).Value = runCount
End If

CodePudding user response：

You can use a loop:

Dim emptyRow As Long
Dim data As Worksheet, wsMH As Worksheet
Dim runCount As Long, n As Long, v

Set data = Sheets("data")
Set wsMH = Worksheets("mining history")
'no need to Activate...
emptyRow = wsMH.Cells(Rows.count, "I").End(xlUp).row   1

If Application.CountA(wsMH.Range("E:E")) = 0 Then
    runCount = 0
Else
    'loop all the entry textboxes
    For n = 1 To 10
        v = Me.Controls("qtytxt" & n).Value
        If Len(v) > 0 And IsNumeric(v) Then runCount = runCount   v
    Next n
    wsMH.Cells(emptyRow, 27).Value = runCount
End If

CodePudding user response：

I suspect you need this:

If qtytxt2.Value = "" Then
    qtytxt2.Value = 0
End If
If qtytxt3.Value = "" Then
    qtytxt3.Value = 0
End If

etc