row totalling venture

[JohnE]

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

 

[crferguson]

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

 

[JohnE]

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

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