• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet_Change procedure to generate few rows of data

Dear Excel Genius

I need the Worksheet_Change procedure to generate the production Batch Card from different products formulations.

The below-linked thread was created and I got the procedure. But now I have some modifications in the source data as well as output structure too.

Target cells F3, C4 and E4 in the sheet "Batch Card", These target cells need to match column B, C and D in the sheet "Formulation" respectively.

From Formulation sheet J5 to CH47 data to extract as per the matches target cells value from Batch card. then it should generate the rows of data from cell A7.

Some product having 1 part and some have 4 part. So depends on the number of RM used in the matched product the row to be adjusted from row 7 in the sheet batch card.

All the products and their parts % is not the same which is mentioned in the formulation sheet column F to I.

Below is some examples batch card image which is created with the older version.

75042 75043

The left side image has two-part and the right image have four-part products. As per the parts and the RM, the rows need to insert or delete. Also, the row height must be adjusted (Part Headings 40 and the RM 30)

After all the parts the total of each product needs to calculate in the next to next row from the last used RM. Example in the second image cells E38 and F38.

As per the attached sheet batch card From row 45 to 49 in column D, data to be filled from the formulation sheet column CU: CV.

The attached sheet has only 43 products but in the actual is nearly 1000 products so the lookup range needs to be extended in the code.

Hope I have explained as much as I can. Need your valuable support to produce the output.

Thanks in advance.


I have posted the same question in another forum too. this is for all your information.