Need VBA Code instead of Let and Lamda functions.

Question

Dear Excel Genius

In the below-linked 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.

https://1drv.ms/x/s!AiSRNak3QYCfg5VVPQlF5FEX_-2vbg?e=wdI072

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,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(by-IFERROR(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.

Answer 1

Boss,

Thanks a lot for your valuable reply.

I have saved the same file as macro free .xlsx format and then drag one row of the let and lambda formula and it is still taking more time for one-row dragging.

Macro free sheet link https://1drv.ms/x/s!AiSRNak3QYCfg5Vi7JQnvS2Snqq_Bg?e=uL6Dny

Please guide me on how to fix or change the sheet to get faster with my 10k rows.

Thanks in advance.

See the GIF below