Search results

Dear VBA Developer, The attached Excel file contains two sheets: Master Data and RM Purchase. In the Master Data sheet, Column B lists the Project Locations, with consumption data spanning from columns 3 to 23. In the RM Purchase sheet, Column 3 also features Project Locations, with price...

G refers Qty and E refers Comapny Name So the real file Y refers to Qty and C refers to Company name. But still output prints Zero in all cells. Sub Cost_Calculation() Dim D@(), B, E, F, R&, L, M@ Dim wsMaster As Worksheet, wsPurchase As Worksheet Dim lastRowMaster As Long...

I changed this and the output is zero again. E = Evaluate(Replace("IF(Y8:Y" & wsPurchase.Rows.Count & ">0,D8:D" & wsPurchase.Rows.Count & ","""")", "#", wsPurchase.Rows.Count)) Boss, please modify the code to function correctly with my actual file.

Boss, I've just changed to `.count` at the end of the code line, but it's showing an error. E = wsPurchase.Evaluate(Replace("IF(Y8:Y#>0,D8:D#,"""")", "#", .Count))

Boss, I've transferred the code to my actual file, but all the output values are zero. Please see the snapshot. I may have made some mistakes while rewriting the code. Could you please check it? Sub Cost_Calculation() Dim D@(), B, E, F, R&, L, M@ Dim wsMaster As Worksheet, wsPurchase As...

It's functioning, boss. I'll transfer it to the actual file and will return if I encounter any issues. Beyond hitting a 'like', there's not much else I can do.

Boss, thanks for your response! I totally get what you're saying. Yes, it’s basically a FIFO-style calculation, and I see why it might feel unnecessary to use VBA for it. The reason I was aiming for a VBA approach is because the data I’m working with can change frequently – company names can...

In the attached sheet K4, I have used a formula to calculate the consumption cost for Company A. In the snapshot below, column D, I manually added the values from columns F and G, which represent the purchase quantity and price, to calculate the cost. The consumption occurred in quantities of...

I appreciate and accept that you are a genius in VBA. Could you provide the code to calculate the cost as requested in the initial post?

In the initial post, I have detailed my requirements with an attachment and provided the expected results through an Excel formula. Please inform me of any additional information you need to provide the VBA code, with or without a dictionary.

Could you include the IF statement and provide the formula that would yield the expected outcome? I meant easy understandable codes that I can transfer to my real file. I have already elaborated in the initial post. Should someone require further details, I will explain the specifics in...

Boss, Thanks for your reply (Expect your reply) The reason I’m asking for a VBA code is that the data can get messy over time. The company names in columns B and E won’t always be in order—they might get shuffled around (like AABBBCADDEFGDFG). If I just use formulas, I’d have to adjust them...

Here is the link to the post in the VBA Macro section for your information. https://chandoo.org/forum/threads/vba-code-to-calculate-consumption-cost-by-matching-company-names-and-purchase-quantities.57862/

Thank you for your time and effort on my post. If your solution can be easily transferred to my original file, then I must use it. However, my actual file contains many companies and many RMs, so creating a table for each company would be difficult for me. As you suggested, I have already posted...

Dear VBA Code Experts, I need a VBA code to dynamically calculate the consumption cost for multiple companies based on matching company names, prices, and purchase quantities. The input data includes company names (B4) with their respective consumption quantities (C4) and company names (E4)...

The formula provided by Copilot AI results in all cells displaying the same value of 0.375. Could someone adjust and provide the corrected formula...

@Peter Bartholomew, thank you for your valuable response and the solution you provided. Transferring your solution to my original sheet is proving to be difficult due to its complex programming. I had hoped that someone could adjust the existing formula to use SUMIF or SUMIFS instead of SUM, to...

If Company A is involved, the entire row of consumption, price, and purchase quantity will only be for Company A, ranging from RM1 to RM10. There will be no clubbing in the same row from RM1 to RM10. The only consideration is FIFO. We must consume the stock from top to bottom, using the...

The snapshot below represents the actual company volume and price quantities. Therefore, the final formula to calculate the cost should utilize the company-specific volume, price and Qty's. It means the company input and output are not will be inline, it will be random and mixed. but the...

According to the snapshot provided, the costs in the left table and the totals in the right table correspond for Companies A and B. However, there is a discrepancy for Company C. Please verify this.

Yes, the below snapshot cost is exactly what I want. But for the volume of each company, I have 5 entries so the 6th will be the stock quantity it should not show in the cost column. When I enter Company A's 6th entry, for example, A remaining volume is 7. If I enter RM1 consumption N19 value...

@Peter Bartholomew Thanks a lot for your effort towards my post. The error of spill was sorted out and company A cost of RM1 is correct. However, the costs of companies B and C are not exactly what I expected. The A total RM1 volume is 47, but the purchase is 50. The remaining 7 must be in...

Dear Excel Genius, As per the below snapshot the formula is working and I calculated the cost of RM1 by using the formula =LET(q,M4:M8,p,Y4:Y8,s,Z4:Z8,sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa...

@rlv01 The code is working perfectly. thanks a lot!

Only the date will be more than one row not all the three values.