Selected Answer
Hi again Malkal
In the attached revised file, I've used your code but modified as in bold.
Essentially the ID is found then it looks for the last value in that row (which may not be in yesterday's column e.g. if a daily operation was missed) and adds or subtracts the UserForm quantity then puts that result in the column for today:
Private Sub CommandButton1_Click()
Dim f As Range
Dim LstCol As Long, Tdy As Range
'Validations
With ComboBox1
If .Value = "" Then
MsgBox "Enter ID"
.SetFocus
Exit Sub
End If
Set f = Range("C:C").Find(.Value, , xlValues, xlWhole, , , False)
If f Is Nothing Then
MsgBox "ID does not exists"
.SetFocus
Exit Sub
End If
End With
With TextBox1
If .Value = "" Or Not IsNumeric(.Value) Then
MsgBox "Enter QTY"
.SetFocus
Exit Sub
End If
End With
'find last value in ID row
LstCol = Cells(f.Row, Columns.Count).End(xlToLeft).Column
'find today's date in row 1
Set Tdy = Rows(1).Find(Date, , xlValues, xlWhole, , , False)
If f Is Nothing Then
MsgBox "Needs a column for today!"
Exit Sub
End If
' quit if last value is in today's column
If LstCol = Tdy.Column Then
MsgBox "Quantity already exits for today"
Exit Sub
End If
'Sum or subtract
With Cells(f.Row, Tdy.Column)
Select Case True
Case OptionButton1 Or OptionButton3
' add qty to last value + put today's column
.Value = Cells(f.Row, LstCol).Value + Val(TextBox1.Value)
Case OptionButton2 Or OptionButton4
' or subtract
.Value = Cells(f.Row, LstCol).Value - Val(TextBox1.Value)
Case Else
MsgBox "No option was selected"
End Select
End With
' empty ID and qty
ComboBox1.Value = ""
TextBox1.Value = ""
End Sub
You might want to allow the value in the Today column to be adjusted by the UserForm but I assumed it was a one-off operation per day.
Hope this is what you meant. If so, please remember to mark this Answer as Selected.
