Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

stop calculation if lastrow doesn't contain DATE

0

Hi

I have macro calculates in  column D for EMPLOYEES sheet based on matching name in column C with TT sheet . so what I want if the lastrows don't contain DATE(TODAY) , then should not calculate for the name . juts running the original code by change amounts for any name in EMPLOYEES sheet based on DATE(TODAY) in column A in TT  sheet for the same name .

Sub sum_or_subtract()
  Dim f As Range, c As Range
  Dim det As String
  Dim v As Double
  Dim lr As Long

  det = LCase("Advance payment")
  With Sheets("TT")
    lr = .Range("B" & Rows.Count).End(3).Row
    For Each c In .Range("B2:B" & lr)
      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)
        If Not f Is Nothing Then
          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
      End If
    Next
  End With
End Sub

thanks

Answer
Discuss

Discussion

Omaran

It's a little difficult to know what you're attempting here- please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data and macros. Then we should be able to give specific help.
John_Ru (rep: 6297) Jul 4, '24 at 4:42 pm
My apologies John  about forgetting the attachment !
Omaran (rep: 10) Jul 4, '24 at 5:05 pm
Thanks Omaran. I'll try to find time later (my time) to check and reply 
John_Ru (rep: 6297) Jul 5, '24 at 7:12 am
Omaran. Sorry- I looked at your file but your question still makes no sense to me.

I'm just not clear what you're trying to avoid- the example data does not show the string "DATE" nor a date at the end of each employee "table" on work
sheet EMPLOYEES (e.g. cell A10 there).

Please clarify your question but note that I'm on holiday from Sunday and will have little time to answer you tomorrow (Saturday) but perhaps Wille will.
John_Ru (rep: 6297) Jul 5, '24 at 5:18 pm
@John

I haven't spent any time on this one because I have the same questions which you have been asking. Also, Omaran uses some coding syntax which I am not familiar with, so that is taking some time to get up to speed with. Maybe if all questions get properly answered I might take a stab at this.

Cheers   :-)
WillieD24 (rep: 567) Jul 5, '24 at 6:53 pm
@Willie- 
On the coding syntax used here, Omaran is using (or perhaps re-using) the numeric form of VBA enumerations. If you look an enumeration up on the Learn.Microsoft.com site (e.g.XlBackground enumeration ), you'll see they are recorded in a table headed Name, Value and Description. I use the Name entry (e.g. xlBackgroundTransparent) rather than the more succinct Value (e.g. 2) to achieve the Description (e.g. Transparent background - for the text on a chart).since it's easier to read when debugging or revisiting after a while.

Hopefully Omaran gets an email notification (or just returns) and updates the original question to clartify.
John_Ru (rep: 6297) Jul 6, '24 at 3:35 am
@John
Thanks for the tip. I took a quick look and this seems to be a good resource (better than some provided by MS). I will explore further when I have more time.
Thanks.
WillieD24 (rep: 567) Jul 6, '24 at 11:37 am
OK
again I edited file and show the cells are  highlighted by red in last sheet how should change in EMPLOYEES sheet .
the amounts will change in column D for EMPLOYEES sheet based on DATE in column A for  TT sheet after matching for each name .
don't change amount for any name in EMPLOYEES sheet if it doesn't contain DATE in TT sheet as in ALI MAHMUD OMAR.
Omaran (rep: 10) Jul 6, '24 at 3:30 pm
Thanks for the clarification, Omaran. Unfortunately it came too late for me to act on it. Hopefully Willie has time to provide a solution. 
John_Ru (rep: 6297) Jul 6, '24 at 3:41 pm
@Omaran
I am still confused as to what you arer trying to do. The added sheet "EXPECTED" is the same as the "EMPLOYEES" sheet except for some red cell fill that has been added. On both sheets Col "A" & "B" contain dates. On the "TT" sheet Col "A" contains dates. There is no example for "if the last rows don't contain DATE, then should not calculate". 
Sorry Omaran but I am going to pass on this one.
WillieD24 (rep: 567) Jul 7, '24 at 8:42 am


Willie,
 The added sheet "EXPECTED" is the same as the "EMPLOYEES" sheet except for some red cell fill that has been added
ofcourse because I said the result in EMPLOYEES sheet in column D and that what the code does it as in OP. but I would implement that based on DATE in TT sheet for each name and ignore name doesn't containd DATE totally.
 On the "TT" sheet Col "A" contains dates. There is no example for "if the last rows don't contain DATE, then should not calculate". 
how there is no date in TT sheet?!!!
yesterday was date when I posted .my date format dd/mm/yyyy (06/07/2024)
anyway thanks for your time.
Omaran (rep: 10) Jul 7, '24 at 2:12 pm
Omaran

Like Willie, I cannot make sense of your example...

In the case of Omar Ali Omar and Ahmed Omar Ali, the red cells (in column D of the EXPECTED sheet) show that the EMPLOYEES sheet should be adjusted by the amounts in column D of TT.

You said above "don't change amount for any name in EMPLOYEES sheet if it doesn't contain DATE in TT sheet as in ALI MAHMUD OMAR"  and D14 and D16 of EXPECTED are NOT change(or red) but there IS a date.in the entry for Ahmed Mahmud Ali in TT- see cell A3 (12/03/2023).

I really don't know what you're trying to do, sorry.
John_Ru (rep: 6297) Jul 12, '24 at 3:56 am
sorry guys !
I do my best to understand my gaol
John
I 'm talking about DATE(TODAY) , not any date.
to answer this
and D14 and D16 of EXPECTED are NOT changed (or red) but there IS a date.in the entry for Ahmed Mahmud Ali in TT- see cell A3 (12/03/2023).
the DATE(TODAY) was 10/07/2024 so just for and D14 and D16 of EXPECTED are NOT change(or red) .
ALI MAHMUD OMAR doesn't contain DATE(TODAY) that's why NOT changed (or red)

in other meaning just calculate for name contains DATE(TODAY) in TT sheet 
and ignore other names don't contain DATE(TODAY)
Note: if I have name repeats more than one time in TT sheet  then calculate for name contains DATE(TODAY) and ignore calculating old  date for the same name .
Omaran (rep: 10) Jul 12, '24 at 8:41 am
Thanks. Still a bit confusing (when read with the file dates) but please see my Answer. Next time, please be careful to explain in your original Question what you're trying to do. Don't rely on it being obvious in the file please.
John_Ru (rep: 6297) Jul 12, '24 at 11:12 am
Add to Discussion

Answers

0
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

Discuss

Discussion

Omaran- I just realised your Question title refers to "lastrow" but please note that my changed code doesn't care where today's date is found in TT (since I removed your test If WorksheetFunction.CountIf(...). If there are 9 tranctions with today's daye for a given name, it will adjust for all 9, not just the last.
John_Ru (rep: 6297) Jul 12, '24 at 11:55 am
sorry John for poor details in my OP .
every thing is great except one thing  .
thank you so much.
Omaran (rep: 10) Jul 12, '24 at 12:25 pm
Glad that helped. Thanks for selecting my Answer Omaran. I don't see the remaining "one thing" but ask another question if you need help
John_Ru (rep: 6297) Jul 12, '24 at 1:41 pm
I wanted calculating based on lastrow for DATE(TODAY) for name contains many data contain DATE(TODAY) because every time run the macro  will repeat calculation have ever calculate, so no need calculate again.
Omaran (rep: 10) Jul 12, '24 at 2:53 pm
Sorry Omaran but I don't understand what you mean. If there are several transactions for today (for an employee) but you want to use just the last, I think you can restore the test I commented out (and add an End If) 
John_Ru (rep: 6297) Jul 12, '24 at 3:34 pm
sorry John !
thanks for you confirmation .
Omaran (rep: 10) Jul 12, '24 at 4:05 pm
Omaran

I did this so that only the last transaction was acted on (see changes in bold)
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)
        ' get last transaction for employee only
        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
 
                << existing code >>
                Else
                ' collect any name(s) NOT found in EMPLOYEES
                Missing = Missing & c.Value & " (" & c.Offset(, 2).Value & "); "
            End If
        End If
      Next c
    End With
    ' say if any name not found
    If Missing <> "" Then MsgBox "Didn't find " & Missing
End Sub

John_Ru (rep: 6297) Jul 13, '24 at 6:53 am
If you're running the macro more than once a day, I suggest that when you find a value for today (and adjust the values in the sheet EMPLOYEES) you set a flag to say that value has been acted on. For example, hide column E of TT but add a "Y" value to E in the transferred row, as a flag (and in your macro only look for rows where Y is NOT in column E of TT).
John_Ru (rep: 6297) Jul 13, '24 at 6:54 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login