Worksheet_Change procedure to generate few rows of data

Anbuselvam

Board Regular
Joined
May 10, 2017
Messages
64
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 in Chandoo.org and I got the procedure on March 2nd 2021. But now I have some modifications in the source data as well as output structure too.

Recent Thread on 18th June 2021 ( Not yet received the solution. So I posted the same here too)​


New Requirements as below.


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 link which is created with the older version.

2 Part Products. Screenshot 2021-03-02 at 5.54.39 PM.jpg

4 Part Products Screenshot 2021-03-02 at 6.01.50 PM.jpg

The first image has two-part and the second 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.

The file link

 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,141,583
Messages
5,707,207
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top