Anbuselvam K
Member
Dear Excel Genius
In the attached excel file Production sheet I am using the below formula (DU:FR) to get the Raw Materials cost per kg by FIFO Method. Due to more rows and columns of data the excel sheet is getting slower in the process for each entry.
For example, I added 150 recent rows and 50 columns of data which also getting slower the process. Actual rows are nearly 10,000 and columns are 200 raw materials.
=LET(q,Consumption[Consumption RM01],p,Purchase[Price RM01],s,Purchase[Qty RM01],sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))s,qa,SUM(q)sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,vsa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(byIFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),),ROWS(DU$6:[@[Cost of RM01]])))
I would like to replace the above formula with a faster VBA code to get the same results. Expecting your support.
Also, I have two VBA Buttons in the same sheet with the recorded macro to extend the consumption and Cost rows. It is also getting slower each time.
Need your valuable code suggestion to make the sheet faster progress.
Thanks in Advance.
In the attached excel file Production sheet I am using the below formula (DU:FR) to get the Raw Materials cost per kg by FIFO Method. Due to more rows and columns of data the excel sheet is getting slower in the process for each entry.
For example, I added 150 recent rows and 50 columns of data which also getting slower the process. Actual rows are nearly 10,000 and columns are 200 raw materials.
=LET(q,Consumption[Consumption RM01],p,Purchase[Price RM01],s,Purchase[Qty RM01],sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))s,qa,SUM(q)sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,vsa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(byIFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),),ROWS(DU$6:[@[Cost of RM01]])))
I would like to replace the above formula with a faster VBA code to get the same results. Expecting your support.
Also, I have two VBA Buttons in the same sheet with the recorded macro to extend the consumption and Cost rows. It is also getting slower each time.
Need your valuable code suggestion to make the sheet faster progress.
Thanks in Advance.
Attachments

796 KB Views: 4