Michael
asked on
Add and update collection items and then carry out calucaltions on collected totals.
Hi, I have a list of orders in a workbook. General order information fields start with a 1, Items/goods for that order start with a 0.
I need to be able to firstly add, then find the total number of a items in the collection and then divide that number by a stack height to work out the number of pallet spaces the order will take up.
I have got so far however now i am stuck. i am not even sure that i am approaching this in the right way, so I am open to suggestions
I need to be able to firstly add, then find the total number of a items in the collection and then divide that number by a stack height to work out the number of pallet spaces the order will take up.
I have got so far however now i am stuck. i am not even sure that i am approaching this in the right way, so I am open to suggestions
Sub thedarkside()
Dim LastRow As Long
With Worksheets("Sheet1").Range("A:O")
LastRow = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
Dim coll As New Collection
Dim keyword(1 To MAX_KWD), stack(1 To MAX_STACK)
Dim TotalVolume, e As Long
Dim p, q, c, r As Range
Set r = Range("A1:A" & LastRow)
keyword(1) = "120"
keyword(2) = "190"
keyword(3) = "240"
keyword(4) = "360"
keyword(5) = "660"
keyword(6) = "770"
keyword(7) = "1100"
stack(1) = "16"
stack(2) = "16"
stack(3) = "20"
stack(4) = "16"
stack(5) = "5"
stack(6) = "5"
stack(7) = "5"
For Each c In r
If c.Value = 1 Then
r = 0
TotalVolume = 0
cd = Cells(c.Row, 8).Value
For i = 1 To MAX_KWD
If (InStr(cd, keyword(i)) > 0) Then
coll.Add Item:=1, Key:=keyword(i)
Exit For
Else: End If
Next i
Else
If c.Value = 0 Then
cd = Cells(c.Row, 3).Value
e = e + 1
For i = 1 To MAX_KWD
If (InStr(cd, keyword(i)) > 0) Then
coll.Add Item:=cd, Key:=keyword(i) / stack(i)
Exit For
Else: End If
Next i
'TotalVolume = Cells(c.row, 19).Value + TotalVolume
'c.Offset(-e, 17).Value = TotalVolume
Else
End If
End If
For i = 1 To Workbooks.Count
Debug.Print Workbooks(i).Name
Next i
' Debug.Print "R =" & r
' Debug.Print "Weight = " & TotalVolume
Next c
End Sub
ASKER
https://www.experts-exchange.com/questions/29165559/Add-and-update-collection-items-and-then-carry-out-calucaltions-on-collected-totals.html?anchorAnswerId=42987124#a42987124
Hi thanks for looking into this and your advise ref (Dim p As Range Dim q As Range). i will go back through my modules and edit as suggested. i am new to vba and programming in general. i have attached the workbook i am trying to edit.
Hi thanks for looking into this and your advise ref (Dim p As Range Dim q As Range). i will go back through my modules and edit as suggested. i am new to vba and programming in general. i have attached the workbook i am trying to edit.
I have not yet looked at your workbook.
Use this as a starting point.
Use this as a starting point.
Sub thedarkside()
Dim LastRow As Long
Dim coll As New Collection
Dim MAX_KWD As Long
Dim MAX_STACK As Long
Dim keyword() As Long
Dim stack() As Long
Dim TotalVolume As Long
Dim e As Long
Dim p As Range
Dim q As Range
Dim c As Range
Dim r As Range
With Worksheets("Sheet1").Range("A:O")
LastRow = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
' I don't know where these values come from so I made them 5 just for this post
MAX_KWD = 5
MAX_STACK = 5
' Note Redim
ReDim keyword(1 To MAX_KWD) As Long
ReDim stack(1 To MAX_STACK) As Long
Set r = Range("A1:A" & LastRow)
keyword(1) = "120"
keyword(2) = "190"
keyword(3) = "240"
keyword(4) = "360"
keyword(5) = "660"
keyword(6) = "770"
keyword(7) = "1100"
stack(1) = "16"
stack(2) = "16"
stack(3) = "20"
stack(4) = "16"
stack(5) = "5"
stack(6) = "5"
stack(7) = "5"
For Each c In r
Select Case c.Value
Case 1
r = 0
TotalVolume = 0
cd = Cells(c.Row, 8).Value
For I = 1 To MAX_KWD
If (InStr(cd, keyword(I)) > 0) Then
coll.Add Item:=1, Key:=keyword(I)
Exit For
Else: End If
Next I
Case 0
cd = Cells(c.Row, 3).Value
e = e + 1
For I = 1 To MAX_KWD
If (InStr(cd, keyword(I)) > 0) Then
coll.Add Item:=cd, Key:=keyword(I) / stack(I)
Exit For
End If
Next I
'TotalVolume = Cells(c.row, 19).Value + TotalVolume
'c.Offset(-e, 17).Value = TotalVolume
Case Else
' I don't know what you want to do when c.Value is not 0 or 1
' End If
' End If
'
'
'
'
' For i = 1 To Workbooks.Count
' Debug.Print Workbooks(i).Name
' Next i
' Debug.Print "R =" & r
' Debug.Print "Weight = " & TotalVolume
End Select
Next c
End Sub
How is "stack height" determined?
Once the calculation is done, what do you want to do with the result?
Once the calculation is done, what do you want to do with the result?
ASKER
Hi the stack hight is determined by the the type of item and it relating volume soo for example i could have 15 x 1100 with a stack height of 5 so it would be 3 pallet space applied to the column 16 (est volume)
number of containers 22 x 1100ltr / stack hight of 5 would be 5 pallets space round up to the whole number
keyword(1) = "120", has a stack hieght of , stack(1) = "16"... ect.
once calculated the answer to go in column 16 of the general infomation line starting with a zero.
number of containers 22 x 1100ltr / stack hight of 5 would be 5 pallets space round up to the whole number
keyword(1) = "120", has a stack hieght of , stack(1) = "16"... ect.
once calculated the answer to go in column 16 of the general infomation line starting with a zero.
How do I know that keyword 120 is associated with the Meadowfield School order?
In addition to my previous question, I don't understand your explanation of how you get to 5. I see that the sum of D3 to D5 is 22, and I see that there is 1100 in column H3, but you seem to be ignoring H4 and H5.
A couple of comments about the workbook:
A couple of comments about the workbook:
- Instead of the (to my old eyes) unreadable text in column F, you can insert a comment in the cell instead, and, optionally, replace the text with something like "Description".
- If you format column D as Text, you wouldn't need the single quotes in front of the numbers.
ASKER
hi, thanks for looking into this and your advice. i was at work before and very rushed my apologies for this confusing file .
i have faced many problems with cleaning up this sheet however this is the last part we are currently looking into.
To give a little back ground.
this sheet is made up of sales orders that are imported from multiple csv files.(1 order per file) . the raw data dose not contain all the data i need, so i am trying to fabricate the missing information needed from the information present in the file and the subject of the email it is downloaded from.
rows starting with 1 (column A) = general order information this is always the top row of the single sales order csv it originated from.
rows starting with 0 below are product lines associated the above row starting 1 above.
when there is another row starting 1 it is treated as a new sales order and it has it product information below starting zero. this keeping going.
the main problem here is i have only been given 1 csv file so far. As this is prep for the the new data import about to go live at work. and i have simply been altering the data manually to create test data. so we are working we test data hence the same order repeated 10 times over.
erm overall aim is to summarize the total of each different product line per order. when divide the summarized total by its stack height
(in this case they are dealing with 4 wheeled waste containers that are sold in signals but delivered stack together.)
row E = quantity.
row H= Item type
meadow field order
12 x 1100 waste containers in total over two different lines. 1100ltrs are stacked in 5's so 12/5= 2.4 which is always rounded up to the nearest whole number so 3.
10 x 660ltr / stack 5 = 2. so the total freight space required is 5 pallet spaces.
to be fair i real appreciate your help but i think i need to approach this a different way. at present i am just trying to replicate some script i wrote in previous module.
i think maybe need to apply each item to a dictionary and update the qty as it travels through the zeros of an sales order.
i cant really alter the core/raw information in the file as it is used at a later date.
Once again thanks.
i have faced many problems with cleaning up this sheet however this is the last part we are currently looking into.
To give a little back ground.
this sheet is made up of sales orders that are imported from multiple csv files.(1 order per file) . the raw data dose not contain all the data i need, so i am trying to fabricate the missing information needed from the information present in the file and the subject of the email it is downloaded from.
rows starting with 1 (column A) = general order information this is always the top row of the single sales order csv it originated from.
rows starting with 0 below are product lines associated the above row starting 1 above.
when there is another row starting 1 it is treated as a new sales order and it has it product information below starting zero. this keeping going.
the main problem here is i have only been given 1 csv file so far. As this is prep for the the new data import about to go live at work. and i have simply been altering the data manually to create test data. so we are working we test data hence the same order repeated 10 times over.
erm overall aim is to summarize the total of each different product line per order. when divide the summarized total by its stack height
(in this case they are dealing with 4 wheeled waste containers that are sold in signals but delivered stack together.)
row E = quantity.
row H= Item type
meadow field order
12 x 1100 waste containers in total over two different lines. 1100ltrs are stacked in 5's so 12/5= 2.4 which is always rounded up to the nearest whole number so 3.
10 x 660ltr / stack 5 = 2. so the total freight space required is 5 pallet spaces.
to be fair i real appreciate your help but i think i need to approach this a different way. at present i am just trying to replicate some script i wrote in previous module.
i think maybe need to apply each item to a dictionary and update the qty as it travels through the zeros of an sales order.
i cant really alter the core/raw information in the file as it is used at a later date.
Once again thanks.
i think maybe need to apply each item to a dictionary and update the qty as it travels through the zeros of an sales order.I may not understand completely, but couldn't that be done with formulas?
Should P3, 4 and 5 all have an estimate or just P3?
ASKER
p2 should contain the total estimate.
p3, 4, 5 should all have estimates. that i have been try to calculate the most most radical way going
this should be done with formulas your right i think i need to review my approach. if i put estimates against each row the then just sum the total in P2... i will look into that. cheers
p3, 4, 5 should all have estimates. that i have been try to calculate the most most radical way going
this should be done with formulas your right i think i need to review my approach. if i put estimates against each row the then just sum the total in P2... i will look into that. cheers
ASKER
erm this is what i have hacked away at.
i have it working. maybe sightly different than your average but it works :)
thanks for your help with this
i have it working. maybe sightly different than your average but it works :)
thanks for your help with this
Sub TotalVolume()
Private Const MAX_KWD = 10
Private Const MAX_STACK = 10
Sub TotalVolume()
Dim keyword(1 To MAX_KWD)
Dim stack(1 To MAX_STACK)
Dim p As Range
Dim q As Range
Dim c As Range
Dim r As Long
Dim TotalVolume As Long
Dim LastRow As Long
Dim cd As String
With Worksheets("Sheet1").Range("A:O")
LastRow = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
keyword(1) = "120"
keyword(2) = "190"
keyword(3) = "240"
keyword(4) = "360"
keyword(5) = "660"
keyword(6) = "770"
keyword(7) = "1100"
stack(1) = "16"
stack(2) = "16"
stack(3) = "20"
stack(4) = "16"
stack(5) = "5"
stack(6) = "5"
stack(7) = "5"
Set q = Range("A1:A" & LastRow)
For Each p In q
If p.Value = 1 Then
Else
If p.Value = 0 Then
cd = Cells(p.Row, 3).Value
For i = 1 To MAX_KWD
If (InStr(cd, keyword(i)) > 0) Then
Cells(p.Row, 16).Value = Cells(p.Row, 5).Value / stack(i)
Exit For
Else: End If
Next i
If (i = MAX_KWD) Then ' not found
Else ' Found
End If
End If
End If
Next
For Each c In q
If c.Value = 1 Then
r = 0
TotalVolume = 0
Else
If c.Value = 0 Then
r = r + 1
TotalVolume = Cells(c.Row, 16).Value + TotalVolume
c.Offset(-r, 15).Value = Application.WorksheetFunction.RoundUp(TotalVolume, 0)
Else
End If
End If
Debug.Print "R =" & r
Debug.Print "TotalVolume = " & TotalVolume
Next c
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice. thanks. always good to see the right way to approach a specific task. some interesting points of learning for me. not sure how relevant the answer is to my initial question...but it was fun getting there.
not sure how relevant the answer is to my initial questionTo make mine like yours you only need to place Sum formulas in P2, P6, etc. Or the code could be changed to add the sums in the '1' rows.
In any case you’re welcome and I’m glad I was able to help.
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
Experts Exchange Top Expert VBA 2018
Experts Exchange Distinguished Expert in Excel 2018
Can you attach your workbook?
Also I want to point out that when you do this
Open in new window
that only r becomes a Range and the rest are by default Variant so you should doOpen in new window
etcetera.