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

--
Thank you,

Jennifer

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


"Jennifer" wrote:

> 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
>
> --
> Thank you,
>
> Jennifer

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

"Jennifer" wrote:

> 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
>
> --
> Thank you,
>
> 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


"JLGWhiz" wrote:

> Already see one goof. Should be For i = 0 To 12 instead of 13.
> The array is zero based.
>
> "Jennifer" wrote:
>
> > 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
> >
> > --
> > Thank you,
> >
> > Jennifer

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

Jennifer


"JLGWhiz" wrote:

> Already see one goof. Should be For i = 0 To 12 instead of 13.
> The array is zero based.
>
> "Jennifer" wrote:
>
> > 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
> >
> > --
> > Thank you,
> >
> > Jennifer

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?

"Jennifer" wrote:

> Hi,
> Does there need to be some sort of sum function for the array?
> --
> Thank you,
>
> Jennifer
>
>
> "JLGWhiz" wrote:
>
> > Already see one goof. Should be For i = 0 To 12 instead of 13.
> > The array is zero based.
> >
> > "Jennifer" wrote:
> >
> > > 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
> > >
> > > --
> > > Thank you,
> > >
> > > Jennifer

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???

Jennifer wrote:
>
> 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
>
> "JLGWhiz" wrote:
>
> > Already see one goof. Should be For i = 0 To 12 instead of 13.
> > The array is zero based.
> >
> > "Jennifer" wrote:
> >
> > > 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
> > >
> > > --
> > > Thank you,
> > >
> > > Jennifer

--

Dave Peterson

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" wrote:

> Hi,
> Does there need to be some sort of sum function for the array?
> --
> Thank you,
>
> Jennifer
>
>
> "JLGWhiz" wrote:
>
> > Already see one goof. Should be For i = 0 To 12 instead of 13.
> > The array is zero based.
> >
> > "Jennifer" wrote:
> >
> > > 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
> > >
> > > --
> > > Thank you,
> > >
> > > 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
--
Thank you,

Jennifer


"JLGWhiz" wrote:

> 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?
>
> "Jennifer" wrote:
>
> > Hi,
> > Does there need to be some sort of sum function for the array?
> > --
> > Thank you,
> >
> > Jennifer
> >
> >
> > "JLGWhiz" wrote:
> >
> > > Already see one goof. Should be For i = 0 To 12 instead of 13.
> > > The array is zero based.
> > >
> > > "Jennifer" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Thank you,
> > > >
> > > > Jennifer

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.


"JLGWhiz" wrote:

> 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" wrote:
>
> > Hi,
> > Does there need to be some sort of sum function for the array?
> > --
> > Thank you,
> >
> > Jennifer
> >
> >
> > "JLGWhiz" wrote:
> >
> > > Already see one goof. Should be For i = 0 To 12 instead of 13.
> > > The array is zero based.
> > >
> > > "Jennifer" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Thank you,
> > > >
> > > > Jennifer