VBA to Get Auto Serial Number As Per Defined Pattern

Hi Experts,

Need your help to accomplish subjected Task, I am not sure whether it’s an Easy or complex task, but if I achieve this, I am sure it will help me a lot as well as saves a lot of time for me…

Basically, we have different kind of products; what we are manufacturing in our plant and each product whenever taken it for production; I will assign a serial number considering current week, current year and some other criteria (Which are explained below) for the Traceability of the products.

Please open the attached file, there are two worksheets named as “Source” and “Current Month Serial#”

Source Work Sheet:-

It contains five different kinds of tables.

Table 1 is distinguish about of what and all the different pattern (text) I am using in my serial number and what does it specifies and Code as per Specification.

Table 2 is “Master Template”, it contains all the product details like product Part Number, Description and respective Serial Number Pattern

Table 3 is “Standard Segment”, Here just I have spitted the serial number pattern as per different criteria like Constant, Year, Week, Numbers etc.,

Table 4 is “Output as per Segment”; here I have used some nested IF Formula to get the real data for Serial Numbering with respect to “Standard Segment” Specification.

Table 5 is “Serial # Prefix”, It just a combined arrangement of “Output As per Segment” (Table 4) and also it’s the Serial Numbering prefix for respective product.

Hope, we are clear on “Source Sheet”. Now I will explain about

Current Month Serial # Work Sheet:-

In this sheet, whatever the column is highlighted in Orange color, those details only I will update it manually and whatever column is highlighted in Green color, I want VBA to fill it automatically by keeping Part Number (Column B) as a Reference.

VBA Can Design in the below Sequence and by meeting following Conditions:-

1. VBA should be applicable only to particular Rows where C Column value is >0 (i.e. Order Qty >0)in the Entire Worksheet

2. VBA first LOOKUP the Product Description (to A Column) and Serial Number Pattern (to H Column) by keeping Part Number as “Lookup Value”, from “Master Template Table” of Source Sheet

3. VBA has to update Today’s Date in G Column to indicate and keep the track like exactly when this Serial Number was updated.

4. It has to pick the “Serial # Prefix” from Source Sheet and check is there any Serial number already allocated with same Prefix (While comparing it should not consider numbers like 001,059 and It has to compare only Constant, Year & Week).
4-A) If There is no serial number allocated with same Prefix, then Serial # should assign like Starting Serial Number DN1245001 (Starting with 001) and Ending Serial Number DN1245100 with whatever the Order Qty is Entered.
4-B) Incase if any serial number is already allocated to previous order with same Prefix, It has to look for Ending Serial # value and has to do +1 with same and assign the Starting Serial # as DN1245101 for current order.

5. Caution: - Everyday I will frequently run the VBA what you are going to develop for me and while Running same, there should not any change or alter can happen in the already updated Serial Number.

6. Request: - I am pretty good in Formulas but I have Zero knowledge in the VBA. So when you write the VBA code, please make the command with the sympol ‘, So that I will be able to understand what exactly it does…

Looking forward Help from your end…

Thanks & Regards,
Rajeshkumar R