When the fragrance bottle volume gets low {less than 5.0} - as determined by W2 - W2 contains the formula =SUM(V2-U2) - I replenish the supply by ordering more bottles & adding that amount to V2 --
So, how can I solve the problem?
Thanks for your help
Thank you for explaining your issue with such clarity,
Zeke. After exploring this thread, I understand that you want to get the information produced in the
W column and transfer back the values in the
V column for the next cycle. Specifically, the remaining amount must be the initial amount for every next cycle.
To demonstrate this situation, I have made a sample dataset. To solve the problem you are addressing, I have developed an
Excel VBA code. You must paste the code into a module. You can create an event to dynamically run the code if any changes occur in the
U column.
Steps:
- Right-click on the sheet name and click on View Code.
- Next, copy and paste the below code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns("U")) Is Nothing Then
Call UpdateReagentAmount
End If
End Sub
- Now, press Alt+F11 to open the VBE and paste the following code in a module.
Code:
Sub UpdateReagentAmount()
Dim ws As Worksheet
Dim reagentAmount As Double
Dim usedAmount As Double
Dim remainingAmount As Double
Dim inputNumber As Double
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(Rows.Count, "U").End(xlUp).Row
If lastRow = 2 Then
reagentAmount = ws.Range("V" & lastRow).Value
usedAmount = ws.Range("U" & lastRow).Value
remainingAmount = reagentAmount - usedAmount
ws.Range("W" & lastRow).Value = remainingAmount
Else
reagentAmount = ws.Range("W" & lastRow - 1).Value
ws.Range("V" & lastRow).Value = ws.Range("W" & lastRow - 1).Value
usedAmount = ws.Range("U" & lastRow).Value
remainingAmount = reagentAmount - usedAmount
ws.Range("W" & lastRow).Value = remainingAmount
End If
If remainingAmount < 5 Then
inputNumber = InputBox("Exceed safety stocks. Add more quantities.")
ws.Range("W" & lastRow).Value = ws.Range("W" & lastRow).Value + inputNumber
MsgBox "Bottles are added."
ThisWorkbook.Save
End If
End Sub
How Does the Code Work:
The code updates the amount of a reagent in a worksheet by considering the used amount. It calculates the remaining amount and writes it in the corresponding cell if there is only one row of data. When there are multiple rows of data, it copies the reagent amount from the previous row to the current row and calculates the remaining amount. If the remaining amount falls below
5, the code prompts the user to add more reagents. The user adds the inputted quantity to the remaining amount, and a message informs that bottles have been added. Finally, the Workbook is saved.
OUTPUT: I am linking a VIDEO for you to understand all the events clearly.
This idea helps you reach your objective. I'm also providing you with the
Workbook used to analyze your problem to assist you in understanding it better. Please get in touch with us again if you have any more questions or issues.
Regards
Lutfor Rahman Shimanto