|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.
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
row = row + 1
Worksheets(2).Range("A1").Value = "ItemID"
Worksheets(2).Range("B1").Value = "Total Projected Procurement"
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
Worksheets(2).Cells(erow, 1) = itemid
Worksheets(2).Cells(erow, 2) = total
|Macro in Excel for inventory solution|