A Super Simple Inventory Automation System

How to create a super simple inventory automation system quickly and easily using the SUMIFS function with VBA.
Here’s the complete VBA code:
Option Explicit

Sub myStock()
Dim prodID As String
Dim stockQty As Long
prodID = InputBox(“Enter product ID to check quantity available.”)

Dim rA As Range, rB As Range, rC As Range

With Worksheets(“Sheet1”)
Set rB = .Range(“B2”, Range(“B” & Rows.Count).End(xlUp))
Set rA = .Range(“A2”, Range(“A” & Rows.Count).End(xlUp))
Set rC = .Range(“C2”, Range(“C” & Rows.Count).End(xlUp))
End With

stockQty = Application.WorksheetFunction.SumIfs( _
rC, rA, prodID, rB, “Purchase”) – Application.WorksheetFunction.SumIfs( _
rC, rA, prodID, rB, “Sale”) + Application.WorksheetFunction.SumIfs( _
rC, rA, prodID, rB, “Return”)

MsgBox “The quantity of item ” & prodID & ” available is ” & stockQty
Range(“F3”) = stockQty
End Sub

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “A Super Simple Inventory Automation System”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.