Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
row totalling venture
Hello. I have a dilemma that is driving me nuts. I have a spreadsheet that
is to total up the rows associated with a product. If the product is in 'Kg' then the total is to be mulitplied by 2204.6. If the product is in pounds, multiply by 2000. The code below is where it all takes place. The original code for either kg or lbs worked fine as there were separate worksheets. But now it is all being located on one worksheet. I placed arrow (-) in front of the original code that worked fine so long as everything was in kg. The other code that is here and commented out is code that I've been trying to use. 'Details' is an array that is used just above this section and has in it the column(s) that I need here. One of the columns indicates whether the product is 'lbs' or 'kg'. If I put the For..Next inside the loop, the For...Next works fine but stops short of bottom of the list (which can vary). The Loop then runs 3 times putting in more then needed. All the data comes from 2 other spreadsheets. One sheet is a listing of all the inventory items and how much there currently is. There could be 135 products for one location and more or less for another location. The other sheet has all the orders listed and the product(s) associated with it. There could be 24 orders placed with a total of 64 products. All of these calcs, etc all work fine on the main worksheet. It is the row totalling of each product that is driving me to my wits end. If the Loop is inside the For...Next, the 'For i' never goes past 0, so the progression down the list adding up the rows does not occur. ' For i = 0 To UBound(Details, 2) - Do Until ActiveCell.Offset(0, 1) = "" - If NoOrders Then - ActiveCell.Value = 0 - Else - ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2204.6" - End If - Loop ' If NoOrders Then ' ActiveCell.Value = 0 ' Else ' ' If UCase(Trim(Details(3, i))) = "LBS" Then ' ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2000" ' ElseIf UCase(Trim(Details(3, i))) = "KG" Then ' ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2204.6" ' Else ' ActiveCell.Value = 0 ' End If ' End If ' Next Since I am at my wits end here on this, I ask for help from the group to see what I am doing wrong on this. Thanks to anyone who responds. .... John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
row totalling venture
On Apr 15, 12:38*pm, JohnE wrote:
Hello. *I have a dilemma that is driving me nuts. *I have a spreadsheet that is to total up the rows associated with a product. *If the product is in 'Kg' then the total is to be mulitplied by 2204.6. *If the product is in pounds, multiply by 2000. *The code below is where it all takes place. *The original code for either kg or lbs worked fine as there were separate worksheets. *But now it is all being located on one worksheet. *I placed arrow (-) in front of the original code that worked fine so long as everything was in kg. * The other code that is here and commented out is code that I've been trying to use. *'Details' is an array that is used just above this section and has in it the column(s) that I need here. *One of the columns indicates whether the product is 'lbs' or 'kg'. If I put the For..Next inside the loop, the For...Next works fine but stops short of bottom of the list (which can vary). *The Loop then runs 3 times putting in more then needed. * All the data comes from 2 other spreadsheets. *One sheet is a listing of all the inventory items and how much there currently is. *There could be 135 products for one location and more or less for another location. *The other sheet has all the orders listed and the product(s) associated with it. *There could be 24 orders placed with a total of 64 products. *All of these calcs, etc all work fine on the main worksheet. *It is the row totalling of each product that is driving me to my wits end. If the Loop is inside the For...Next, the 'For i' never goes past 0, so the progression down the list adding up the rows does not occur. ' * *For i = 0 To UBound(Details, 2) - * * * Do Until ActiveCell.Offset(0, 1) = "" - * * * * * If NoOrders Then - * * * * * * * *ActiveCell.Value = 0 - * * * * * *Else - * * * * * * * *ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2204.6" - * * * * * *End If - * * * Loop ' * * * * * *If NoOrders Then ' * * * * * * * * * *ActiveCell.Value = 0 ' * * * * * *Else ' ' * * * * * * * *If UCase(Trim(Details(3, i))) = "LBS" Then ' * * * * * * * * * *ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2000" ' * * * * * * * *ElseIf UCase(Trim(Details(3, i))) = "KG" Then ' * * * * * * * * * ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2204.6" ' * * * * * * * *Else ' * * * * * * * * * *ActiveCell.Value = 0 ' * * * * * * * *End If ' * * * * * *End If ' * *Next Since I am at my wits end here on this, I ask for help from the group to see what I am doing wrong on this. * Thanks to anyone who responds. ... John That's pretty hard to follow, but I'll take a shot at it if you can answer a couple of questions... 1. Am I right in understanding that the orders are in one sheet and that you're trying to come up with two separate sums: one for orders in kgs, and another for orders in lbs? 2. Where are you going to put those results? 3. What are you summing up prior to multiplying by 2204.6 or 2000? Is it a number of orders, or how much weight was in each order? Where are those values on the sheet? Basically, I think you gave way too muhc info on one hand that isn't needed, and not nearly enough info on the other hand. Answering those questions might help, but it would be best to just say what you're wanting to do specifically. Example: I have two columns on a sheet. In column A are the numbers for how much my orders weigh. In column B is the unit of measurement, either kgs or lbs. I need to add up a total of how many kgs and how many lbs were ordered and then place the answers in cells C1 and D1. Thanks! Cory |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
row totalling venture
Cory, thanks for the response. To answer your questions;
1) At the end of each product row there is a total calculated then multiplied by the proper number (depending on kg or lbs). There is only one cell the total goes into. 2) The results go into one cell at the end of each product row. One row, one cell. Next row, one cell, and so on. 3) What is being summed up are the number of products ordered. If the detail shows product XYZ (lbs) for order ABC at 5, then the total would show 10,000. If is was kg, then it would be 11,023. .... John " wrote: On Apr 15, 12:38 pm, JohnE wrote: Hello. I have a dilemma that is driving me nuts. I have a spreadsheet that is to total up the rows associated with a product. If the product is in 'Kg' then the total is to be mulitplied by 2204.6. If the product is in pounds, multiply by 2000. The code below is where it all takes place. The original code for either kg or lbs worked fine as there were separate worksheets. But now it is all being located on one worksheet. I placed arrow (-) in front of the original code that worked fine so long as everything was in kg. The other code that is here and commented out is code that I've been trying to use. 'Details' is an array that is used just above this section and has in it the column(s) that I need here. One of the columns indicates whether the product is 'lbs' or 'kg'. If I put the For..Next inside the loop, the For...Next works fine but stops short of bottom of the list (which can vary). The Loop then runs 3 times putting in more then needed. All the data comes from 2 other spreadsheets. One sheet is a listing of all the inventory items and how much there currently is. There could be 135 products for one location and more or less for another location. The other sheet has all the orders listed and the product(s) associated with it. There could be 24 orders placed with a total of 64 products. All of these calcs, etc all work fine on the main worksheet. It is the row totalling of each product that is driving me to my wits end. If the Loop is inside the For...Next, the 'For i' never goes past 0, so the progression down the list adding up the rows does not occur. ' For i = 0 To UBound(Details, 2) - Do Until ActiveCell.Offset(0, 1) = "" - If NoOrders Then - ActiveCell.Value = 0 - Else - ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2204.6" - End If - Loop ' If NoOrders Then ' ActiveCell.Value = 0 ' Else ' ' If UCase(Trim(Details(3, i))) = "LBS" Then ' ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2000" ' ElseIf UCase(Trim(Details(3, i))) = "KG" Then ' ActiveCell.FormulaR1C1 = "=SUM(R[0]C[" & -(NumberOfOrders) & "]:R[0]C[-1]) * 2204.6" ' Else ' ActiveCell.Value = 0 ' End If ' End If ' Next Since I am at my wits end here on this, I ask for help from the group to see what I am doing wrong on this. Thanks to anyone who responds. ... John That's pretty hard to follow, but I'll take a shot at it if you can answer a couple of questions... 1. Am I right in understanding that the orders are in one sheet and that you're trying to come up with two separate sums: one for orders in kgs, and another for orders in lbs? 2. Where are you going to put those results? 3. What are you summing up prior to multiplying by 2204.6 or 2000? Is it a number of orders, or how much weight was in each order? Where are those values on the sheet? Basically, I think you gave way too muhc info on one hand that isn't needed, and not nearly enough info on the other hand. Answering those questions might help, but it would be best to just say what you're wanting to do specifically. Example: I have two columns on a sheet. In column A are the numbers for how much my orders weigh. In column B is the unit of measurement, either kgs or lbs. I need to add up a total of how many kgs and how many lbs were ordered and then place the answers in cells C1 and D1. Thanks! Cory |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NPV of cashflows to assess contribution to a joint venture | Excel Worksheet Functions | |||
Totalling volumes per box | Excel Worksheet Functions | |||
Sub totalling in VBA | Excel Programming | |||
totalling | Excel Discussion (Misc queries) | |||
Totalling formulas | Excel Worksheet Functions |