PasteBin Hyperlink

I’ve received a code that has a “grasp sheet”, a “worth sheet”, and a “non worth sheet” (in addition to about 100 different “retailer particular” sheets which can be a part of the method earlier than this, however haven’t any significance right here).

The grasp sheet is a data-set of retailer numbers (column “A”), Barcodes (column “C”), and Actions (column “H”). The shops are all “labeled” as V (worth) or NV (Non Worth). The macro copies the grasp sheet then removes duplicate UPCs to construct each the Worth and Non Worth sheets. Then it runs from the underside of the worth sheet up and:

1. cycles from the underside of the grasp sheet as much as discover see if the motion is 0

   If Mstr_Mvmt = Zero Then Else

2. if the grasp motion <> Zero then examine if the barcode matches the UPC from the worth sheet.

   If V_Barcode.Worth <> Mstr_Sht.Cells(t, 3).Worth Then Else

3. in the event that they do match, do a VLookup of the shop quantity in opposition to the “Class_Rng” (classification vary) to see if the shop quantity on the grasp sheet is a worth retailer.

   If WorksheetFunction.VLookup(Mstr_Str_Num, Class_Rng, 2, False) = "V" Then

4. whether it is, add the grasp motion to the worth motion.

   .Cells(r, "H").Worth = Mstr_Mvmt + V_Mvmt

This course of then repeats for the Non Worth sheet. Sorry for the dumb format, however code is required to put up. It’s simpler to learn within the hyperlink.

The grasp sheet has proper at 90,000 traces and the opposite two (with duplicates eliminated) have about 900. So in whole the collection of “if’s” runs by means of 162,000,000 rows. I had the If’s on one line utilizing and/or instructions, however I do not know that they sped something up in any respect, and after I began rearranging logic I broke them out to see if it will assist something. I do know that there’s an argument about if v. case, and a few say case could also be a millisecond sooner however I desire if statements, nevertheless, I’d gladly swap groups on this case (no pun supposed) if it helped.

There are different sheets on within the “Mstr_Bk” so I attempted breaking that into a brand new guide in order that these ~100 sheets weren’t there simply as litter, and the code made it by means of the entire Worth sheet for the primary time and the ensuing file is what I wanted, however then Excel crashed whereas it was operating the Non Worth cycle and I misplaced that little bit of code and needed to come back right here earlier than re-writing it, within the hopes that there’s a higher resolution.

Additionally, I realized VB although trial and error, so be light. My VB vocabulary is not the very best, and if there may be something seen that may be a poor follow, please let me know. (I do know Possibility Express is one thing I needs to be doing, however I’ve by no means taken the time to be taught the way it capabilities, and to this point, I have not been too beat up by not utilizing it.)