Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum values in listbox
The user chooses from a combo box and it then fills a list box with 6 colomns
that correlate to the combo box. My question is how do i write a formula to sum the value that show up in column 4 (percent of ratio) in the listbox. Here is the code that i use to fill the listbox. i want the sum total to show up in a lable1 when the combo box is loaded. Private Sub LoadData() With lstData ..Clear Ration = cboRation.Value For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.Date1) .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.Ingredient) .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Percent_of_ration) .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Ration_ID) .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.Pounds) End If Next End With End Sub -- Thank you, Jennifer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum values in listbox
Private Sub LoadData()
With lstData ..Clear Ration = cboRation.Value Total = 0 For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id data = source.Cells(index, eBoxes.Date1) total = total + data .List(.ListCount - 1, 2) = data data = source.Cells(index, eBoxes.Ingredient) total = total + data .List(.ListCount - 1, 3) = data data = source.Cells(index, eBoxes.Percent_of_ration) total = total + data .List(.ListCount - 1, 4) = data data = source.Cells(index, eBoxes.Ration_ID) total = total + data .List(.ListCount - 1, 5) = data data = source.Cells(index, eBoxes.Pounds) total = total + data .List(.ListCount - 1, 6) = data End If Next End With End Sub "Jennifer" wrote: The user chooses from a combo box and it then fills a list box with 6 colomns that correlate to the combo box. My question is how do i write a formula to sum the value that show up in column 4 (percent of ratio) in the listbox. Here is the code that i use to fill the listbox. i want the sum total to show up in a lable1 when the combo box is loaded. Private Sub LoadData() With lstData .Clear Ration = cboRation.Value For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.Date1) .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.Ingredient) .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Percent_of_ration) .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Ration_ID) .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.Pounds) End If Next End With End Sub -- Thank you, Jennifer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum values in listbox
I am getting the error varible not defined for "Data" is that because it
should be lstData? When i switch it to lstData then the i get another error message and something about it being "Null" I need some help . . . i want to sum column 3 in the listbox and have it show the sum value in a label. HELP -- Thank you, Jennifer "Joel" wrote: Private Sub LoadData() With lstData .Clear Ration = cboRation.Value Total = 0 For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id data = source.Cells(index, eBoxes.Date1) total = total + data .List(.ListCount - 1, 2) = data data = source.Cells(index, eBoxes.Ingredient) total = total + data .List(.ListCount - 1, 3) = data data = source.Cells(index, eBoxes.Percent_of_ration) total = total + data .List(.ListCount - 1, 4) = data data = source.Cells(index, eBoxes.Ration_ID) total = total + data .List(.ListCount - 1, 5) = data data = source.Cells(index, eBoxes.Pounds) total = total + data .List(.ListCount - 1, 6) = data End If Next End With End Sub "Jennifer" wrote: The user chooses from a combo box and it then fills a list box with 6 colomns that correlate to the combo box. My question is how do i write a formula to sum the value that show up in column 4 (percent of ratio) in the listbox. Here is the code that i use to fill the listbox. i want the sum total to show up in a lable1 when the combo box is loaded. Private Sub LoadData() With lstData .Clear Ration = cboRation.Value For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.Date1) .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.Ingredient) .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Percent_of_ration) .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Ration_ID) .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.Pounds) End If Next End With End Sub -- Thank you, Jennifer |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum values in listbox
The problem is not with "Data". "Data" is only a intermediate variable. The
problem is with your original code. There is a problem with the statement source.Cells(index, eBoxes.Date1) I don't know if source or eboxes is defined. "Jennifer" wrote: I am getting the error varible not defined for "Data" is that because it should be lstData? When i switch it to lstData then the i get another error message and something about it being "Null" I need some help . . . i want to sum column 3 in the listbox and have it show the sum value in a label. HELP -- Thank you, Jennifer "Joel" wrote: Private Sub LoadData() With lstData .Clear Ration = cboRation.Value Total = 0 For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id data = source.Cells(index, eBoxes.Date1) total = total + data .List(.ListCount - 1, 2) = data data = source.Cells(index, eBoxes.Ingredient) total = total + data .List(.ListCount - 1, 3) = data data = source.Cells(index, eBoxes.Percent_of_ration) total = total + data .List(.ListCount - 1, 4) = data data = source.Cells(index, eBoxes.Ration_ID) total = total + data .List(.ListCount - 1, 5) = data data = source.Cells(index, eBoxes.Pounds) total = total + data .List(.ListCount - 1, 6) = data End If Next End With End Sub "Jennifer" wrote: The user chooses from a combo box and it then fills a list box with 6 colomns that correlate to the combo box. My question is how do i write a formula to sum the value that show up in column 4 (percent of ratio) in the listbox. Here is the code that i use to fill the listbox. i want the sum total to show up in a lable1 when the combo box is loaded. Private Sub LoadData() With lstData .Clear Ration = cboRation.Value For index = 2 To source.Rows.Count If Ration = source.Cells(index, 6) Then .AddItem source.Cells(index, 1) 'id .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.Date1) .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.Ingredient) .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Percent_of_ration) .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Ration_ID) .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.Pounds) End If Next End With End Sub -- Thank you, Jennifer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox Values | Excel Discussion (Misc queries) | |||
Values from a ListBox | Excel Programming | |||
Fill values into a listbox matching selected values from a combobox | Excel Programming | |||
Compare Listbox values with Collection values | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |