Creating a loop

[Jennifer]

Hi Guys,
Can you help?
I have created this code but I have never created a loop before so I need
some direction:
I have 13 text boxes I need it to check for a value and subtract it from the
'percent'
Thank you, Jennifer

Private Sub GetSum1()
Dim Ration As Variant
Dim Percent As String
With Worksheets("Database")
Ration = txtRFID.Value
'Sheets("Rations").Activate
Percent = WorksheetFunction. _
SumIf(Range("ProduceItemDatabase"), Ration, Range("BxsToMarket"))
If Me.TextBox1 = "" Then
Exit Sub
Else
Me.lblTotal = Percent - TextBox1.Value
End If

End With
End Sub

 

[JLGWhiz]

This is untested, but hopefully will run without error. If you get one, just
post back.

Private Sub GetSum1()
Dim Ration As Variant
Dim Percent As String
With Worksheets("Database")
Ration = txtRFID.Value
'Sheets("Rations").Activate
Percent = WorksheetFunction. _
SumIf(Range("ProduceItemDatabase"), Ration, Range("BxsToMarket"))
TBarr = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, _
TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11, _
TextBox12, TextBox13)
For i = 0 To 13
If Me.TBarr(1)Value = "" Then
Exit Sub
Else
Me.lblTotal = Percent - TBarr(i).Value
End If
Next
End With
End Sub

 

[JLGWhiz]

Already see one goof. Should be For i = 0 To 12 instead of 13.
The array is zero based.

 

[Jennifer]

Thanks a bunch-
Close but I am getting a few errors in getting those I tried to problem
solve the best I understand and this is where I am stuck: Thank you a bunch!

Dim Ration As Variant
Dim Percent As String

With Worksheets("Database")
Ration = txtRFID.Value
'Sheets("Rations").Activate
Percent = WorksheetFunction. _
SumIf(Range("ProduceItemDatabase"), Ration, Range("BxsToMarket"))
TBarr = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, _
TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11, _
TextBox12, TextBox13)
For i = 0 To 12
If Me.TextBox1.Value = "" Then
Exit Sub
Else
Me.lblTotal = Percent - TBarr(i).Value ********It is saying
type mismatch
End If
Next
End With
End Sub--
Thank you,

Jennifer

 

[Jennifer]

Hi,
Does there need to be some sort of sum function for the array?

 

[JLGWhiz]

No, no sum function. All the array does is set up an index system so you can
use the i varaiable for your loop. I did that in a hurry and did not take a
close look at the whole thing. Just threw in the basics to loop through the
13 listboxes. What error message does it throw and on what line?

 

[Dave Peterson]

I'm not sure what you're doing or where your controls are...

But I guessed that the textboxes (from the Control toolbox toolbar) were on the
same worksheet as the label.

I put 13 textboxes on a worksheet, a label and a commandbutton (all from the
control toolbox toolbar).

This is the code under the commandbutton:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim myTBSum As Double
Dim myTBVal As String

myTBSum = 0

For iCtr = 1 To 13
myTBVal = Me.OLEObjects("Textbox" & iCtr).Object.Value
If IsNumeric(myTBVal) Then
myTBSum = myTBSum + myTBVal
Else
Beep
End If
Next iCtr

Me.Label1.Caption = Format(myTBSum, "0.00")

End Sub

Maybe it'll give you an idea???

 

[JLGWhiz]

Hope this has all the typos cleared. You might have to make the array all on
one line instead of three. Sometimes the attenuation line doesn't work. But
you would know if it turns red when you paste it in.

Private Sub GetSum1()
Dim Ration As Variant
Dim Percent As String
With Worksheets("Database")
Ration = txtRFID.Value
'Sheets("Rations").Activate
Percent = WorksheetFunction. _
SumIf(Range("ProduceItemDatabase"), Ration, Range("BxsToMarket"))
TBarr = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, _
TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11, _
TextBox12, TextBox13)
For i = 0 To 12
If Me.TBarr(i)Value = "" Then
Exit Sub
Else
Me.lblTotal = Percent - TBarr(i).Value
End If
Next
End With
End Sub

 

[Jennifer]

Method or Data Member not Found error and it highlights
If Me.TBarr(1).Value = "" Then

is the 1st error i hit. if i change that TBarr to Textbox1.value
it will run until this when I get a 'type mismatch' error
Me.lblTotal = Percent - TBarr(i).Value

 

[JLGWhiz]

The TBarr(1) should have been TBarr(i). I fixed that in the revised code
that is posted. The type mismatch error is telling you that your variable
"Percent" and the TextBox value are two different data types. One is
probably a string and the other a number. You can try this modified line:

Me.lblTotal = Percent - CDbl(TBarr(i).Value)

The CDbl coerces the TextBox value to a number.

 

[Jennifer]

I am pretty confused
I am getting the error message: Member or data member not found for this line
myTBVal = Me.OLEObjects("Textbox" & iCtr).Object.Value

To explain just a bit further I am working in a form, the user selects a
fruit from a combo box when he makes this selection a label (lblTotal) is
then filled with the boxes of inventory on hand of that fruit. From there the
user clicks into textboxes (13 possible) and enters how many he wants to send
to each market. There is a text box for each market. As he enters for example
30 in txtbox1 i would like that number (30) to be subtracted from the total
inventory number and reflected in the lblTotal. This may have made it more
confusing. Thank you for the help. Jennifer

 

[Dave Peterson]

It's a userform--inside the VBE?

I created a userform with 13 textboxes, a commandbutton and a label:

Option Explicit
Private Sub CommandButton1_Click()

Dim iCtr As Long
Dim myTBSum As Double
Dim myTBVal As String

myTBSum = 0

For iCtr = 1 To 13
myTBVal = Me.Controls("Textbox" & iCtr).Value
If IsNumeric(myTBVal) Then
myTBSum = myTBSum + myTBVal
Else
Beep
End If
Next iCtr

Me.Label1.Caption = Format(myTBSum, "0.00")

End Sub

I am pretty confused
I am getting the error message: Member or data member not found for this line
myTBVal = Me.OLEObjects("Textbox" & iCtr).Object.Value

To explain just a bit further I am working in a form, the user selects a
fruit from a combo box when he makes this selection a label (lblTotal) is
then filled with the boxes of inventory on hand of that fruit. From there the
user clicks into textboxes (13 possible) and enters how many he wants to send
to each market. There is a text box for each market. As he enters for example
30 in txtbox1 i would like that number (30) to be subtracted from the total
inventory number and reflected in the lblTotal. This may have made it more
confusing. Thank you for the help. Jennifer