So here is my code. It's super scrappy, but it works.:
Set Rng = Sheets("Pallet Inventory").Range("D6:D10000")
For Each Dn1 In Rng
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][1][\-][0-9][0-9]" _
And Dn1.Value <> "" Then
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D5").Value
Else
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D5").Value
End If
End If
If Dn1.Offset(0, -1).Value = "-" And Dn1.Font.Color = vbBlack Then
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D5").Value
Else
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D5").Value
End If
End If
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][2][\-][0-9][0-9]" Then GoTo NextSet
Next Dn1
NextSet:
For Each Dn1 In Rng
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][2][\-][0-9][0-9]" _
And Dn1.Value <> "" Then
If Dn1.Offset(0, -1).Value = "05-02-01" Then
Sheets("Grower Pounds Weekly").Range("D6").Value = "0"
End If
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D6").Value
Else
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D6").Value
End If
End If
If Dn1.Offset(0, -1).Value = "-" And Dn1.Font.Color = vbBlack Then
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D6").Value
Else
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D6").Value
End If
End If
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][3][\-][0-9][0-9]" Then GoTo NextSet1
Next Dn1
I am trying to gather the poundage data from "pallet inventory" and place this information into sheet "grower pounds weekly"
So the two code blocks above gather poundage info from May 1st and May 2nd from grower 001 and place them into a table like this: (sheet "grower pounds weekly")
<tbody>
</tbody>
This is Sheet "Pallet Inventory"
<tbody>
</tbody>
The "-" in "pallet code" column signifies a change to the pallet. Either adding or subtracting cases. Some pallets have numerous changes, other have none whatsoever.
So 05-01-01 means May 1st, first pallet of the day. We only need to focus on the first four digits: 05-01-## means May 1st, 05-02-## means May 2nd.
If the "quantity" column text colours are red or blue, we don't need to worry about those, only if they are black do we need to add them to our "grower pounds weekly" sheet.
If the Grade is 11L, 11S, 11P, 11J, we need to multiply them by 11 (these are 11lb cases), if they are anything else, we need to multiply them by 28 (28lb cases)
I need to do this for every grower. So the rest of "pallet inventory" will be filled in similarly, but for different growers, 003,004,005 and so on.
So based on my "Pallet Inventory" example, the "Grower Pounds Weekly" should look like this:
<tbody>
</tbody>
I really appreciate your help in this!!!
Set Rng = Sheets("Pallet Inventory").Range("D6:D10000")
For Each Dn1 In Rng
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][1][\-][0-9][0-9]" _
And Dn1.Value <> "" Then
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D5").Value
Else
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D5").Value
End If
End If
If Dn1.Offset(0, -1).Value = "-" And Dn1.Font.Color = vbBlack Then
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D5").Value
Else
Sheets("Grower Pounds Weekly").Range("D5").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D5").Value
End If
End If
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][2][\-][0-9][0-9]" Then GoTo NextSet
Next Dn1
NextSet:
For Each Dn1 In Rng
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][2][\-][0-9][0-9]" _
And Dn1.Value <> "" Then
If Dn1.Offset(0, -1).Value = "05-02-01" Then
Sheets("Grower Pounds Weekly").Range("D6").Value = "0"
End If
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D6").Value
Else
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D6").Value
End If
End If
If Dn1.Offset(0, -1).Value = "-" And Dn1.Font.Color = vbBlack Then
If Dn1.Offset(0, 1) = "11P" Or Dn1.Offset(0, 1) = "11J" Or Dn1.Offset(0, 1) = "11S" Or Dn1.Offset(0, 1) = "11L" Then
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 11 + Sheets("Grower Pounds Weekly").Range("D6").Value
Else
Sheets("Grower Pounds Weekly").Range("D6").Value = Dn1.Value * 28 + Sheets("Grower Pounds Weekly").Range("D6").Value
End If
End If
If Dn1.Offset(0, -1).Value Like "[0][5][\-][0][3][\-][0-9][0-9]" Then GoTo NextSet1
Next Dn1
I am trying to gather the poundage data from "pallet inventory" and place this information into sheet "grower pounds weekly"
So the two code blocks above gather poundage info from May 1st and May 2nd from grower 001 and place them into a table like this: (sheet "grower pounds weekly")
001 | 002 | 003 | 004 | 005 | 006 | 007 | 008 | 009 | 010 | 011 | 012 | 013 | 014 | |
may 1 | "D5" | |||||||||||||
May 2 | "D6" | |||||||||||||
may 3 | ||||||||||||||
may 4 | ||||||||||||||
may 5 | ||||||||||||||
may 6 | ||||||||||||||
may 7 | ||||||||||||||
may 8 | ||||||||||||||
may 9 | ||||||||||||||
may 10 | ||||||||||||||
may 11 | ||||||||||||||
so on and so forth until | ||||||||||||||
July 15 | ||||||||||||||
<tbody>
</tbody>
This is Sheet "Pallet Inventory"
001 | 002 | 003 | 004 | 005 | 006 | 007 | So on and so Forth Until 014 | |||||||||||||||||
Pallet Inventory | Quantity | Grade | Traceability | Quantity | Grade | Traceability | Quantity | Grade | Traceability | Quantity | Grade | Traceability | Quantity | Grade | Traceability | Quantity | Grade | Traceability | Quantity | Grade | Traceability | |||
05-01-01 | 20 "D6" | SS1 | ||||||||||||||||||||||
- | -5 | SS1 | ||||||||||||||||||||||
- | 10 | SS1 | ||||||||||||||||||||||
- | 20 | 11L | ||||||||||||||||||||||
05-01-02 | 20 | SS2 | ||||||||||||||||||||||
- | 10 | 11P | ||||||||||||||||||||||
- | 15 | 11S | ||||||||||||||||||||||
- | 5 | SS1 | ||||||||||||||||||||||
05-01-03 | 48 | SS1 | ||||||||||||||||||||||
05-01-04 | 48 | SS2 | ||||||||||||||||||||||
05-01-05 | 48 | SS1 | ||||||||||||||||||||||
05-01-06 | 48 | SS2 | ||||||||||||||||||||||
- | 10 | SS2 | ||||||||||||||||||||||
- | 10 | SS2 |
<tbody>
</tbody>
The "-" in "pallet code" column signifies a change to the pallet. Either adding or subtracting cases. Some pallets have numerous changes, other have none whatsoever.
So 05-01-01 means May 1st, first pallet of the day. We only need to focus on the first four digits: 05-01-## means May 1st, 05-02-## means May 2nd.
If the "quantity" column text colours are red or blue, we don't need to worry about those, only if they are black do we need to add them to our "grower pounds weekly" sheet.
If the Grade is 11L, 11S, 11P, 11J, we need to multiply them by 11 (these are 11lb cases), if they are anything else, we need to multiply them by 28 (28lb cases)
I need to do this for every grower. So the rest of "pallet inventory" will be filled in similarly, but for different growers, 003,004,005 and so on.
So based on my "Pallet Inventory" example, the "Grower Pounds Weekly" should look like this:
001 | 002 | 003 | 004 | 005 | 006 | 007 | 008 | 009 | 010 | 011 | 012 | 013 | 014 | |
may 1 | 4583 | 3248 | ||||||||||||
May 2 | ||||||||||||||
may 3 | ||||||||||||||
may 4 | ||||||||||||||
may 5 | ||||||||||||||
may 6 | ||||||||||||||
may 7 | ||||||||||||||
may 8 | ||||||||||||||
may 9 | ||||||||||||||
may 10 | ||||||||||||||
may 11 | ||||||||||||||
so on and so forth until | ||||||||||||||
July 15 | ||||||||||||||
<tbody>
</tbody>
I really appreciate your help in this!!!