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