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