Find the cost by VBA (FIFO Method)

Anbuselvam

Board Regular
Joined
May 10, 2017
Messages
97
Dear Excel Genius

I have produced some products in my company which are having or use different raw materials purchased at different prices. I want to calculate the product's price per kg by using the (Raw Materials) FIFO Method.

Below is the example data I have calculated Products 1 to 5's RM 1 price manually in column X, where I need to calculate RM 1 to 10 for the Products 1 to 5. (actual RM more than 100, just for an example I added 10 RM Only)

Products 1 to 5 RM Usage is in the below image1 (Actual will be more than 10,000 productions are there to calculate)

RM 1 to RM 10 Purchase qty with the price is below image2

Products 1 to 5 RM 1 cost manual entry is in the below image3 (Attachment linked file Column X)

In Attachment Column W, I need each product's RM 1 to RM 10 Cost summation to be shown by VBA Code. Attached is the file link for your reference.

We can split the data into different sheets (One sheet of consumption and another sheet of purchase date if required)
I want the expected results in Column W by VBA Code.

File link
 

Attachments

  • Screenshot 2023-01-15 at 4.32.38 PM.png
    Screenshot 2023-01-15 at 4.32.38 PM.png
    61.1 KB · Views: 21
  • Screenshot 2023-01-15 at 4.33.33 PM.png
    Screenshot 2023-01-15 at 4.33.33 PM.png
    78.4 KB · Views: 22
  • Screenshot 2023-01-15 at 4.34.17 PM.png
    Screenshot 2023-01-15 at 4.34.17 PM.png
    22.6 KB · Views: 20

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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
Back
Top