my scrappy data gathering code needs work

[mrmarc]

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

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

 

[BlueHornet]

Don't take this the wrong way, but my own take on this is that your entire data-gathering and reporting scheme needs to be reworked.

Consider the data that you need to capture: ship date (?); receipt date (?); grower; product grade; pallet ID; # packages on the pallet (?); size of the packages (?); weight of the packages (?); product being grown (?); pallet and package revisions (?) - whatever it is - and create a table to capture the information. "Comment" and "Memo" type information that is not germane to the data processing and summation can also be captured in the table, but in a separate column that will not be processed or figure in calculations.

What you've diagrammed, to the degree that I can make any sense of it at all (admittedly very little), seems to be a very complicated method of first recording data (in a complicated way), and then adding to the complexity in the reporting function. If you develop this and maintain it in a business, then you'll be married to it forever, because no one else will be able to make sense of it, modify it or explain it.

You can make a fairly sophisticated table of the data that needs to be entered, maintained and revised, and then from that data either use pivot tables to do your summary and reporting functions, or work something else from a normalized, auditable and comprehensible data table. You've got to start with data that can be comprehended, and I don't see that here.

 

[mrmarc]

Blue Hornet.

This is produce traceability, which is completely complicated in and of itself. This is part of an inventory management program spanned across three terminals connected to a network. I don't expect anyone to understand our business or why I chose these methods. Trust me, they are for good reasons.

I appreciate your concerns, but all I'm looking for is help with my code. If you have questions with something to do with my code or the process that I need the code to execute, I am more than willing to respond and help where necessary.

 

[mrmarc]

Can anyone help me out?

 

[J.Ty.]

Dear Marc,

I can very much using formulas, but I do not work with VBA.

As a matter of fact, what prevents me from helping you are bad design decisions in your spreadsheets.

The first is that you do not adhere to the First Normal Form - pallet inventory code indeed represents two independent pieces of information: the date and the serial number of the pallet. This immediately makes everything much harder, but formulas could do it.

Next, it is a bad idea to use a dash to say "the same pallet as before", because it creates a nonlocal reference: if you find such a row, you do not know which pallet it is about and must potentially look far away to determine this. (Have you thought what happens if somebody accidentally sorts your data by first column? All dashes get separated from their main entries and the information is lost forever...)

In the column "Quantity" you have entry 20 "D6", which is clearly a compound of two pieces of information, and it is always a difficult (and prone to errors) task to separate them again.

The final problem is completely unsolvable using formulas: in the column "Quantity" you encode crucial information using font color. Formulas principally do not know about colors and thus this form of recording important differences makes this task udoable for me. (Have you thought what happens if one of your employees is color-blind?)

Summing up, the quality of the design of your spreadsheet is very low and this is why a seemingly simple problem of summing entries from one day turns out
to be extremely complicated.

I am sorry, but I agree with BlueHornet.

J.Ty.