| 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() 'We first define the starting row of our action Row = 2 'Next we define a variable to capture the total total = 0 'Now we run the loop 'We also define when the looping should stop Dim ItemNo As String ItemNo = InputBox("Enter the item no.") Do While Cells(Row, 1) <> "" If Cells(Row, 1) = ItemNo Then total = total + Cells(Row, 8).Value End If Row = Row + 1 Loop 'Next we activate sheet2 Worksheets(2).Activate 'Then we find the first empty row in sheet2 erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'Now we automate the data entry into sheet2 from sheet1 Worksheets(2).Cells(erow, 1) = ItemNo Worksheets(2).Cells(erow, 2) = total End Sub |
|
| Macro in Excel for inventory solution | |