| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a loop
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 -- Thank you, Jennifer "Dave Peterson" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a loop
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 Jennifer wrote: 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 -- Thank you, Jennifer "Dave Peterson" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with creating a loop | Setting up and Configuration of Excel | |||
| Need help creating Loop | Excel Discussion (Misc queries) | |||
| Creating a loop | Excel Programming | |||
| Creating a loop | Excel Programming | |||
| Creating a For Loop | Excel Programming | |||
Linear Mode
