Free Excel Training Videos

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


Interesting Inventory Applications
Macro in Excel for inventory solution