Inventory Solution Using VBA

Macro in Excel for inventory solution

We need to find the total projected procurement from the inventory data provided. The data consists of Item No, Name, Lot No, Opening Quantity, Projected Sales, Loss/Rejection, Projected Closing Balance (CB), Projected Procurement, Rate and Amount. The calculated data has to be placed in a new worksheet, let’s say, Sheet2. Also all the calculations should happen with a single click.
Watch the training video below to see how the complete process works.

 

Macro Code:
Private Sub CommandButton1_Click()
Dim row As Integer
Dim total As Integer
Dim itemid As String
row = 2
total = 0

itemid = InputBox(“Enter the item ID”, “Item ID”)

Do While Cells(row, 1).Value <> “”
If Cells(row, 1) = itemid Then
total = total + Cells(row, 7).Value
End If
row = row + 1
Loop
Worksheets(2).Activate
Worksheets(2).Range(“A1”).Value = “ItemID”
Worksheets(2).Range(“B1”).Value = “Total Projected Procurement”
Worksheets(2).Range(“A1:B1”).Columns.AutoFit
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
Worksheets(2).Cells(erow, 1) = itemid
Worksheets(2).Cells(erow, 2) = total

End Sub
Further reading:
Inventory

Leave a Reply

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