Sum values of six textboxes out of 9 textboxes with a condition

[kelly mort]

Hello and merry Christmas to you (if you do celebrate it)

I need help with this challenge :

There are these 9 textboxes on my userform that I want to add values from six of them and place the result in the 10th textbox.

Now these are the rules for the summing :

1. Add the first 4 boxes if all first 4 are none empty. Then added the first two smallest values from the rest of the textboxes.
2. If one of the first 4 boxes is empty then add the three none empty ones and add first three smallest values from the remaining textboxes.
3. If two boxes are empty from the first 4 textboxes then add the two none empty and add first 4 smallest values of the rest of the textboxes. This should follow in that order.

I am stacked with the algorithm.

I have the feeling someone here can fix it for me.

Thanks in advance
Kelly

 

[RSpin]

I think this will do what you're wanting:

Private Sub TextBox1_Change()
Calc
End Sub
Private Sub Calc()
Dim AA As Integer
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim OutPut As Double
Dim TBused(1 To 9) As Variant
AA = 0
'test for 6 textboxes filled in
For BB = 1 To 9
    If IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
        AA = AA + 1
    End If
Next BB
If AA < 6 Then
    Exit Sub
End If
' Find the 6 numbers and add them together
OutPut = 0
For AA = 1 To 6
    For BB = 1 To 9
        If TBused(BB) <> "Used" And IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
            If BB > 4 Then
                CC = 0
                For DD = 5 To 9
                    If TBused(DD) <> "Used" And IsNumeric(Me.Controls("TextBox" & DD).Value) = True Then
                        If CC = 0 Then
                            CC = DD
                        Else
                            If Val(Me.Controls("TextBox" & DD).Value) < Val(Me.Controls("TextBox" & CC).Value) Then
                                CC = DD
                            End If
                        End If
                    End If
                Next DD
                OutPut = OutPut + Val(Me.Controls("TextBox" & CC).Value)
                TBused(CC) = "Used"
                Exit For
            Else
                OutPut = OutPut + Val(Me.Controls("TextBox" & BB).Value)
                TBused(BB) = "Used"
                Exit For
            End If
        End If
    Next BB
Next AA
'put the sum of the 6 numbers into the textbox
Me.TextBox10.Value = OutPut
End Sub
Private Sub TextBox2_Change()
Calc
End Sub
Private Sub TextBox3_Change()
Calc
End Sub
Private Sub TextBox4_Change()
Calc
End Sub
Private Sub TextBox5_Change()
Calc
End Sub
Private Sub TextBox6_Change()
Calc
End Sub
Private Sub TextBox7_Change()
Calc
End Sub
Private Sub TextBox8_Change()
Calc
End Sub
Private Sub TextBox9_Change()
Calc
End Sub

This will automatically update textbox10 as the inputs to textboxes 1-9 are changed.

 

[kelly mort]

I think this will do what you're wanting:

Private Sub TextBox1_Change()
Calc
End Sub
Private Sub Calc()
Dim AA As Integer
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim OutPut As Double
Dim TBused(1 To 9) As Variant
AA = 0
'test for 6 textboxes filled in
For BB = 1 To 9
    If IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
        AA = AA + 1
    End If
Next BB
If AA < 6 Then
    Exit Sub
End If
' Find the 6 numbers and add them together
OutPut = 0
For AA = 1 To 6
    For BB = 1 To 9
        If TBused(BB) <> "Used" And IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
            If BB > 4 Then
                CC = 0
                For DD = 5 To 9
                    If TBused(DD) <> "Used" And IsNumeric(Me.Controls("TextBox" & DD).Value) = True Then
                        If CC = 0 Then
                            CC = DD
                        Else
                            If Val(Me.Controls("TextBox" & DD).Value) < Val(Me.Controls("TextBox" & CC).Value) Then
                                CC = DD
                            End If
                        End If
                    End If
                Next DD
                OutPut = OutPut + Val(Me.Controls("TextBox" & CC).Value)
                TBused(CC) = "Used"
                Exit For
            Else
                OutPut = OutPut + Val(Me.Controls("TextBox" & BB).Value)
                TBused(BB) = "Used"
                Exit For
            End If
        End If
    Next BB
Next AA
'put the sum of the 6 numbers into the textbox
Me.TextBox10.Value = OutPut
End Sub
Private Sub TextBox2_Change()
Calc
End Sub
Private Sub TextBox3_Change()
Calc
End Sub
Private Sub TextBox4_Change()
Calc
End Sub
Private Sub TextBox5_Change()
Calc
End Sub
Private Sub TextBox6_Change()
Calc
End Sub
Private Sub TextBox7_Change()
Calc
End Sub
Private Sub TextBox8_Change()
Calc
End Sub
Private Sub TextBox9_Change()
Calc
End Sub

This will automatically update textbox10 as the inputs to textboxes 1-9 are changed.

Great great!!!


Exactly and even powerful than I expected.

One thung:
I want the value in textboxe10 cleared when the used textboxes drops below 6.

Thanks

 

[RSpin]

Glad I could help.
To get the textbox cleared, change:

If AA < 6 Then
    Exit Sub
End If

to:

If AA < 6 Then
    Me.TextBox10.Value = ""
    Exit Sub
End If

 

[kelly mort]

Great!

I need explanation to :

1. Dim TBUsed (1 To 9) As Variant
2. "Used".

I don't understand what they are doing
Regards
Kelly

 

[RSpin]

No problem.

"Dim TBUsed(1 to 9) as Variant" is creating an array. In this case, it's similar to creating 9 variables that can hold data. When one of the numbers, from a textbox, is put into the sum, the corrisponding slot in the array is marked with the word "Used" (so if the number in TextBox3 is added to the sum, then TBUsed(3) would be marked "Used"). When the code checks for a number in a textbox, it is also checking the array, in the corrisponding slot, to see if it is marked "Used" and if so, it skips the number. The purpose is to make sure no number gets used more than once in the sum.

I hope that helps.

 

[kelly mort]

Very useful. Does it mean that when I start my textboxes from say textbox11 to textbox19 for the nine textboxes then I have to get

Dim TBused ( 11 To 19) As Variant?

Looking very tricky. Lol

 

[RSpin]

Yes it does.

If you're planning to use the code for textboxes outside of the 1 to 10 range, then there are several other things you would also need to change as well:

Both "For BB = 1 to 9 " loops would need the numbers to change to the textboxes being used.
The "If BB > 4 then" line would need to change to the 4th textbox.
The "For DD = 5 to 9" would need to change to the last 5 textboxes.
The line "me.TextBox10.value = Output" would need to change to reflect the new textbox that will show the sum.
And each new textbox would a sub that runs the Calc sub.

Sorry for it being so complicated.

 

[kelly mort]

No problem.

Challenge accepted

 

[RSpin]

ok, tried to reduce the things you would need to change in this new iteration of the code:

Private Sub TextBox1_Change()
Calc
End Sub
Private Sub Calc()
Dim FirstBox As Integer
Dim LastBox As Integer
Dim OutPutBox As Integer
Dim AA As Integer
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim OutPut As Double
Dim TBused() As Variant


FirstBox = 1
LastBox = 9
OutPutBox = 10


ReDim TBused(FirstBox To LastBox)
AA = 0
'test for 6 textboxes filled in
For BB = FirstBox To LastBox
    If IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
        AA = AA + 1
    End If
Next BB
If AA < 6 Then
    Me.Controls("TextBox" & OutPutBox).Value = ""
    Exit Sub
End If
' Find the 6 numbers and add them together
OutPut = 0
For AA = 1 To 6
    For BB = FirstBox To LastBox
        If TBused(BB) <> "Used" And IsNumeric(Me.Controls("TextBox" & BB).Value) = True Then
            If BB > (FirstBox + 3) Then
                CC = 0
                For DD = (FirstBox + 4) To LastBox
                    If TBused(DD) <> "Used" And IsNumeric(Me.Controls("TextBox" & DD).Value) = True Then
                        If CC = 0 Then
                            CC = DD
                        Else
                            If Val(Me.Controls("TextBox" & DD).Value) < Val(Me.Controls("TextBox" & CC).Value) Then
                                CC = DD
                            End If
                        End If
                    End If
                Next DD
                OutPut = OutPut + Val(Me.Controls("TextBox" & CC).Value)
                TBused(CC) = "Used"
                Exit For
            Else
                OutPut = OutPut + Val(Me.Controls("TextBox" & BB).Value)
                TBused(BB) = "Used"
                Exit For
            End If
        End If
    Next BB
Next AA
'put the sum of the 6 numbers into the textbox
Me.Controls("TextBox" & OutPutBox).Value = OutPut
End Sub
Private Sub TextBox2_Change()
Calc
End Sub
Private Sub TextBox3_Change()
Calc
End Sub
Private Sub TextBox4_Change()
Calc
End Sub
Private Sub TextBox5_Change()
Calc
End Sub
Private Sub TextBox6_Change()
Calc
End Sub
Private Sub TextBox7_Change()
Calc
End Sub
Private Sub TextBox8_Change()
Calc
End Sub
Private Sub TextBox9_Change()
Calc
End Sub

In this version, the only things you should need to do is:
Change the value of FirstBox to the first textbox number. (11 in the case above)
Change the value of LastBox to the last textbox number. (19 in the case above)
Change the value of OutPutBox to the textbox number of the textbox you want to show the results.
And, add a sub that runs Calc to each of the new textboxes. Such as this for textbox11:

Private Sub TextBox11_Change()
Calc
End Sub

One piece of code that has changed is: "Dim TBused() as variant" and then "Redim TBused(FirstBox to LastBox)".
What this does is create the array, but with no initial settings. Then change the array to use the size of that is needed, based on the variables you have put in. The reason this is two steps is that VBA throws fits when you initially create the array using variables ( I don't know why it cares, but it does.)