Selected Answer
Omaran
From the Discussion under your question. it seems that your original question and file don't explain what you want. It seems you want to adjust the EMPLOYEES sheet if a transaction on TT has today's date (and do nothing otherwise). Sadly your last clarification confuses that by referring to a run date of 10/07/2024 (dd/mm/yyyy) but the dates in your file are 06/07/2024.
To detect and act on dates in worksheet TT which match the current date (which can be obtained in VBA by the function DATE), the attached revised file has the following code changes (in bold, with comments). Note that I added a new variable Missing to detect any errors in the names against transactions in TT:
Sub sum_or_subtract()
Dim f As Range, c As Range
Dim det As String
Dim v As Double
Dim lr As Long
Dim Missing As String
det = LCase("Advance payment")
With Sheets("TT")
lr = .Range("B" & Rows.Count).End(3).Row
For Each c In .Range("B2:B" & lr)
' don't use this but rely on DATE test
'If WorksheetFunction.CountIf(.Range(c, .Range("B" & lr)), c.Value) = 1 Then
Set f = Sheets("EMPLOYEES").Range("C:C").Find(c.Value, , xlValues, xlWhole, , , False)
' check if found on EMPLOYEES sheet
If Not f Is Nothing Then
' and if the date matches today's....
If IsDate(c.Offset(0, -1).Value) _
And c.Offset(0, -1).Value = Date Then
' ...if so, adjust values for today's transaction
v = c.Offset(, 2).Value * IIf(Left(LCase(c.Offset(, 1).Value), Len(det)) = det, -1, 1)
f.Offset(0, 1).Value = f.Offset(0, 1).Value + v
f.Offset(2, 1).Value = f.Offset(0, 1).Value + f.Offset(1, 1).Value
End If
Else
' collect any name(s) NOT found in EMPLOYEES
Missing = Missing & c.Value & " (" & c.Offset(, 2).Value & "); "
End If
Next c
End With
' say if any name not found
If Missing <> "" Then MsgBox "Didn't find " & Missing
End Sub
Note too that in TT, yellow cells A2 and A5 are set to the current date, using worksheet function:
=TODAY()
so they should adjust the EMPLOYEE values but green cell B5 has a deliberate error (...OMAR2") to demonstrate the error mesage at the end of the code (so only the transaction for B2 currently does that.
I'm not sure why you don't change the LAST and BLAANCE dates in EMPLOYEES (or record the tranaction type). Nor do I know if this method will be good for auditting purposes but I leave you to sort those issues as appropriate to your organisation.
Hope this fixes your problem. If so, please remember to mark this Answer as Selected
