Click here to Skip to main content
15,041,391 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My code works as SUMIFS function does.
Below code just completes this below formula that is.
=SUMIFS(Inventory!$C$2:$C$500000,Inventory!$B$2:$B$500000,'Current Stock'!B2,Inventory!$A$2:$A$500000,'Current Stock'!A2,Inventory!$G$2:$G$500000,"="&'Current Stock'!G2)


How to minus this below formula from above one.
-SUMIFS(Sold!$C$2:$C$500000,Sold!$B$2:$B$500000,'Current Stock'!B2,Sold!$A$2:$A$500000,'Current Stock'!A2,Sold!$G$2:$G$500000,"="&'Current Stock'!G2)



The complete formula will be as.
=SUMIFS(Inventory!$C$2:$C$500000,Inventory!$B$2:$B$500000,'Current Stock'!B2,Inventory!$A$2:$A$500000,'Current Stock'!A2,Inventory!$G$2:$G$500000,"="&'Current Stock'!G2)-SUMIFS(Sold!$C$2:$C$500000,Sold!$B$2:$B$500000,'Current Stock'!B2,Sold!$A$2:$A$500000,'Current Stock'!A2,Sold!$G$2:$G$500000,"="&'Current Stock'!G2)


Any solution will be greatly appreciated. If there would be an another solution using array then please share.

Code
Dim dict As Object
    Dim searchrange As Range
    
    With Sheet1
        Dim last_y As Long
        Dim i As Long
        Set dict = CreateObject("Scripting.Dictionary")
        last_y = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To last_y

        dict(.Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 7).Value) = dict(.Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 7).Value) + .Cells(i, 3).Value
                
        Next i
    End With
    
    With Sheet3
        last_y = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 2 To last_y
        .Cells(i, 3).Value = dict(.Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 7).Value)
        Next i
    End With


What I have tried:

I have been finding an way online but could not find any thing.
Posted
Comments
Maciej Los 11-Feb-21 13:22pm
   
Sorry, but i don't get you... Do you want to achieve that by using VBA code or via formulas?
The Mentee 12-Feb-21 5:42am
   
via VBA code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900