• 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.

VBA Code to replace Let and Lamda Functions.

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,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.


Dear Excel Genius

I would like to feed some more information about the LET and LAMBDA function and how it works. The below-linked thread has all the information from the scratch on why the LET and LAMBDA functions were created and it is the way of calculations.

I am expecting your valuable reply with the solution.