Msgbox when value for this year to date greater than this time last year

[Ironman]

Hi

The below sheet 'Training Log' extract contains the following info:

Column A (starting Row 12, currently Row 8748 and added to daily) contains dates for every day since Jan 1 1998.
Column B contains either text either beginning with "REST", "OTHER" or a route description.
Column C contains a number greater than zero (miles run) or is blank.

Fri, 26 Nov 2021	REST
Sat, 27 Nov 2021	OTHER (IB)
Sun, 28 Nov 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Blackhills/Golf Course/R down Beck Foot Lane/Wagon Lane/Down LLC to Hirst Wood Lock (to post)/Back up to 5-Rise Locks/Down to 3-Rise & over Br/Brown Cow/Main Rd all the way back home	12.2
Mon, 29 Nov 2021	OTHER (IB)
Tue, 30 Nov 2021	Cull. Rd/Viaduct/Stn Rd Haworth Rd/Shay Lane/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (18/12/2018)	5.0
Wed, 1 Dec 2021	REST
Thu, 2 Dec 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)	10.6

I'd be very grateful for 2 macros, which are quite similar:

Macro 1
Looks at Columns A and B from Jan 1 in the current year (whichever year that is) to today's date. It then counts the number of cells with a route description (i.e. excludes text strings beginning with "REST" and "OTHER").
It will then do the same for Columns A and B from Jan 1 in the previous year (whichever year that is) and compares the two totals.
If a route description has been entered in the last row of Column B and the total for the current year is greater than the total for the previous year by less than 2 then a msgbox appears with "You have been running more times this year than last, as at [whatever today's date last year is e.g. Dec 3 2020]".

Macro 2
Looks at Columns A and C from Jan 1 in the current year (whichever year that is) to today's date. It then sums the values of those cells and does the same for Columns A and C from Jan 1 in the previous year (whichever year that is) and compares the two.
If a value greater than zero has been entered in the last row of Column C and the value for the current year is greater than the value for the previous year by less than 10 then a msgbox appears with "You have run more miles this year than last, as at [whatever today's date last year is e.g. Dec 3 2020]".

Hope you can help?

Many thanks!

 

[Ironman]

I have re-thought the above and have identified what might be a more straightforward method of constructing a solution, using sheet 'Daily Tracking' instead of 'Training Log'.

The first range (Column A) shows headings for the second range.

MrExcel (no codes).xlsx
	A
369	TRUE YR
370	AGE/YR#
371	DAY YTD
372	MLG YTD
373	RANK/40
374	PROJ. Y/E
375	ACTL. Y/E
376	RANK/40
377	AVE RUN
378	RANK/40
379	DAYS
Daily Tracking

Cell Formulas
Range	Formula
A373,A378,A376	A373	="RANK/"&YearEndRank

The second range relevant rows are
- AN369:CC369 which are years from 2020 to 2061 (extract just goes up to 2030)
- AN372:CC372 which are year to date mileages totals as above
- AN379:CC379 which are year to date run totals as above

MrExcel (no codes).xlsx
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
369	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
370	58/39	59/40	60/41	61/42	62/43	63/44	64/45	65/46	66/47	67/48	68/49
371	339	338
372	347	399
373	25	23
374	353	453
375	347	399
376	25	24
377	6.4	9.7
378	11	2
379	54	41
Daily Tracking

Cell Formulas
Range	Formula
AN371:AO371	AN371	= YEP_LastDate - $A$2 + 1 + (YEP_LastDate>$A60)*(2-MONTH(DATE(AN$1,2,29)))
AN372:AO372	AN372	= SUMPRODUCT(--($A2:$A367<=YEP_LastDate),AN2:AN367)
AN373:AO373	AN373	=RANK(AN372,$B$372:$CC$372)
AN374:AO374	AN374	=AN372 + (SUMPRODUCT(--ISNUMBER($A2:$A367),--($A2:$A367>DATE(YEAR($A$2)+1,1,AN371-90)),AM2:AM367) + SUMPRODUCT(--($A2:$A367>YEP_LastDate-90),--($A2:$A367<=YEP_LastDate),AN2:AN367))/90 * (DaysInCurYear-AN371)
AN375:AO375	AN375	=SUMPRODUCT(--(ISNUMBER($A$1:$A$370)),(AN$1:AN$370))
AN376:AO376	AN376	=RANK(AN375,$B$375:$CC$375)
AN377:AO377	AN377	=AN375/AN379
AN378:AO378	AN378	=RANK(AN377,$B$377:$CC$377)
AN379:AO379	AN379	=COUNTIF(AN2:AN367,">0")

Named Ranges
Name	Refers To	Cells
CurYTD	=OFFSET('Daily Tracking'!$Y$375,0,YEAR(TODAY())-2005)	AO376:AO377, AN376
DAY_YTD	=INDEX('Daily Tracking'!$371:$371,1,DailyTrackingColumn)	AO374
EntRng	=OFFSET('Daily Tracking'!$Y$2:$Y$367,0,YEAR(TODAY())-2005)	AO379, AO374:AO375, AO372
PreYTD	='Daily Tracking'!$B$375:INDEX('Daily Tracking'!$375:$375,1,DailyTrackingColumn-1)	AN376:AO376
'Daily Tracking'!solver_opt	='Daily Tracking'!$S$375	AN376:AO376

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO379	Cell Value	>=330	text	YES
AO379	Cell Value	between 275 and 329	text	YES
AO379	Cell Value	between 243 and 274	text	YES
AO377:AO378	Cell Value	between 99 and 199	text	YES
AO377:AO378	Cell Value	between 200 and 365	text	YES
AO375	Cell Value	between 1000 and 1249	text	YES
AO375	Cell Value	between 1250 and 1499	text	YES
AO375	Cell Value	>=1500	text	YES
AO372	Expression	=AO372>=LARGE($C$375:$CC$375,1)	text	YES
AO372	Expression	=AO372>=LARGE($C$375:$CC$375,2)	text	YES
AO372	Expression	=AO372>=LARGE($C$375:$CC$375,3)	text	YES
AN379	Cell Value	>=330	text	YES
AN379	Cell Value	between 275 and 329	text	YES
AN379	Cell Value	between 243 and 274	text	YES
AN377:AN378	Cell Value	between 99 and 199	text	YES
AN377:AN378	Cell Value	between 200 and 365	text	YES
AN375	Cell Value	between 1000 and 1249	text	YES
AN375	Cell Value	between 1250 and 1499	text	YES
AN375	Cell Value	>=1500	text	YES
AN372	Expression	=AN372>=LARGE($C$375:$CC$375,1)	text	YES
AN372	Expression	=AN372>=LARGE($C$375:$CC$375,2)	text	YES
AN372	Expression	=AN372>=LARGE($C$375:$CC$375,3)	text	YES
CD381:CD383,B379:AE379,AP379:CD379	Cell Value	>=330	text	YES
CD381:CD383,B379:AE379,AP379:CD379	Cell Value	between 275 and 329	text	YES
CD381:CD383,B379:AE379,AP379:CD379	Cell Value	between 243 and 274	text	YES
B377:AE378,AP377:CC378	Cell Value	between 99 and 199	text	YES
B377:AE378,AP377:CC378	Cell Value	between 200 and 365	text	YES
B375:AE375,AP375:CC375	Cell Value	between 1000 and 1249	text	YES
B375:AE375,AP375:CC375	Cell Value	between 1250 and 1499	text	YES
B375:AE375,AP375:CC375	Cell Value	>=1500	text	YES
B372:AE372,AP372:CC372	Expression	=B372>=LARGE($C$375:$CC$375,1)	text	YES
B372:AE372,AP372:CC372	Expression	=B372>=LARGE($C$375:$CC$375,2)	text	YES
B372:AE372,AP372:CC372	Expression	=B372>=LARGE($C$375:$CC$375,3)	text	YES

As per my original post, I'm looking for a message box in 2 situations

1 - when the result of the formula in the cell for the current year (currently Col AO372, next year will be AP372) exceeds the previous year (currently Col AN372, next year will be AO372) by less than 10 then a msgbox appears with "You have run more miles this year than last, as at [whatever today's date last year is e.g. Dec 5 2020]".

2 - when the result of the formula in the cell for the current year (currently Col AO379, next year will be AP379) exceeds the previous year (currently Col AN379, next year will be AO379) by less than 2 then a msgbox appears with "You have been running more times this year than last, as at [whatever today's date last year is e.g. Dec 5 2020]".

I hope you can help? I'm more than happy to upload the workbook if this helps. Please just ask, thank you.

Many thanks!

 

[NoSparks]

Good evening Paul,
Here's a couple of macros you can try.
Added an extra message box to know what the calculated values are as the existing data I have of yours doesn't satisfy the requirements for the requested message boxes.
Don't know if that's because of the data or my macros. Anyway, give this a shot and let me know, good luck.

Sub For_Macro1()
    Dim ws As Worksheet
    Dim lr As Long, Rng As Range
    Dim ThisYrStartdate As Date, ThisYrEnddate As Date
    Dim LastYrStartdate As Date, LastYrEnddate As Date
    Dim ThisYrCount As Long
    Dim LastYrCount As Long
    
' sheet to work on
Set ws = Sheets("Training Log")
' range to work with
With ws
    lr = .Range("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Rng = .Range("A12:C" & lr)
End With
' dates to work with
ThisYrStartdate = DateSerial(Year(Date), 1, 1)
ThisYrEnddate = Date
LastYrStartdate = DateAdd("yyyy", -1, ThisYrStartdate)
LastYrEnddate = DateAdd("yyyy", -1, Date)
' calculation of thisyrcount
ThisYrCount = Application.WorksheetFunction.CountIfs(Rng.Columns(3), ">0", Rng.Columns(1), ">=" & ThisYrStartdate)
' calculation of lastyrcount
LastYrCount = Application.WorksheetFunction.CountIfs(Rng.Columns(3), ">0", Rng.Columns(1), ">=" & LastYrStartdate, Rng.Columns(1), "<=" & LastYrEnddate)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'for info only during testing, delete when satisfied
MsgBox "Last years count as at " & LastYrEnddate & " was " & LastYrCount & vbLf & _
       "This years count is " & ThisYrCount, , "For Info Only"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' the message box
With ws.Cells(lr, 2)
    If Left(.Value, 4) <> "REST" And Left(.Value, 5) <> "OTHER" Then
        If ThisYrCount > LastYrCount And ThisYrCount <= LastYrCount + 2 Then
            MsgBox "You have been running more times this year than last, as at " & LastYrEnddate
        End If
    End If
End With

End Sub

Sub For_Macro2()
    Dim ws As Worksheet
    Dim lr As Long, Rng As Range
    Dim ThisYrStartdate As Date, ThisYrEnddate As Date
    Dim LastYrStartdate As Date, LastYrEnddate As Date
    Dim ThisYrDist As Single
    Dim LastYrDist As Single

Application.ScreenUpdating = False
' sheet to work on
Set ws = Sheets("Training Log")
' range to work with
With ws
    lr = .Range("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Rng = .Range("A12:C" & lr)
End With
' dates to work with
ThisYrStartdate = DateSerial(Year(Date), 1, 1)
ThisYrEnddate = Date
LastYrStartdate = DateAdd("yyyy", -1, ThisYrStartdate)
LastYrEnddate = DateAdd("yyyy", -1, Date)
' calculation of thisyrdist
ThisYrDist = Application.WorksheetFunction.SumIfs(Rng.Columns(3), Rng.Columns(3), ">0", Rng.Columns(1), ">=" & ThisYrStartdate)
' calculation of lastyrcount
LastYrDist = Application.WorksheetFunction.SumIfs(Rng.Columns(3), Rng.Columns(3), ">0", Rng.Columns(1), ">=" & LastYrStartdate, Rng.Columns(1), "<=" & LastYrEnddate)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'for info only during testing, delete when satisfied
MsgBox "Last years distance as at " & LastYrEnddate & " was " & LastYrDist & vbLf & _
       "This years distance is " & ThisYrDist, , "For Info Only"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' the message box
If ws.Cells(lr, 3).Value > 0 Then
    If ThisYrDist > LastYrDist And ThisYrDist <= LastYrDist + 10 Then
        MsgBox "You have run more miles this year than last, as at " & LastYrEnddate
    End If
End If

End Sub

 

[Ironman]

Many thanks for helping me Nolan!

I inserted your 2 macros in the Worksheet_Change event.

Both test msgboxes worked fine so I commented them out and I had to rename some of the "dim"s to avoid duplicates.

Other than that, everything's as it was when I pasted it into my worksheet, as below:

'Macro 1 - MsgBox "You have been running more times this year than last..."
    Dim ws As Worksheet
    Dim lr As Long, Rng1 As Range
    Dim ThisYrStartdate As Date, ThisYrEnddate As Date
    Dim LastYrStartdate As Date, LastYrEnddate As Date
    Dim ThisYrCount As Long
    Dim LastYrCount As Long
 
' sheet to work on
Set ws = Sheets("Training Log")
' range to work with
With ws
    lr = .Range("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Rng1 = .Range("A12:C" & lr)
End With
' dates to work with
ThisYrStartdate = DateSerial(Year(Date), 1, 1)
ThisYrEnddate = Date
LastYrStartdate = DateAdd("yyyy", -1, ThisYrStartdate)
LastYrEnddate = DateAdd("yyyy", -1, Date)
' calculation of thisyrcount
ThisYrCount = Application.WorksheetFunction.CountIfs(Rng1.Columns(3), ">0", Rng1.Columns(1), ">=" & ThisYrStartdate)
' calculation of lastyrcount
LastYrCount = Application.WorksheetFunction.CountIfs(Rng1.Columns(3), ">0", Rng1.Columns(1), ">=" & LastYrStartdate, Rng1.Columns(1), "<=" & LastYrEnddate)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'for info only during testing, delete when satisfied
'MsgBox "Last years count as at " & LastYrEnddate & " was " & LastYrCount & vbLf & _
'       "This years count is " & ThisYrCount, , "For Info Only"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' the message box
With ws.Cells(lr, 2)
    If Left(.Value, 4) <> "REST" And Left(.Value, 5) <> "OTHER" Then
        If ThisYrCount > LastYrCount And ThisYrCount <= LastYrCount + 2 Then
            MsgBox "You have been running more times this year than last, as at " & LastYrEnddate
        End If
    End If
End With


'Macro 2 - MsgBox "You have run more miles this year than last..."
    Dim ws1 As Worksheet
    Dim lr2 As Long, Rng2 As Range
    Dim ThisYrStartdate1 As Date, ThisYrEnddate1 As Date
    Dim LastYrStartdate1 As Date, LastYrEnddate1 As Date
    Dim ThisYrDist As Single
    Dim LastYrDist As Single

Application.ScreenUpdating = False
' sheet to work on
Set ws1 = Sheets("Training Log")
' range to work with
With ws1
    lr2 = .Range("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Rng2 = .Range("A12:C" & lr2)
End With
' dates to work with
ThisYrStartdate1 = DateSerial(Year(Date), 1, 1)
ThisYrEnddate1 = Date
LastYrStartdate1 = DateAdd("yyyy", -1, ThisYrStartdate)
LastYrEnddate1 = DateAdd("yyyy", -1, Date)
' calculation of thisyrdist
ThisYrDist = Application.WorksheetFunction.SumIfs(Rng2.Columns(3), Rng2.Columns(3), ">0", Rng2.Columns(1), ">=" & ThisYrStartdate)
' calculation of lastyrcount
LastYrDist = Application.WorksheetFunction.SumIfs(Rng2.Columns(3), Rng2.Columns(3), ">0", Rng2.Columns(1), ">=" & LastYrStartdate, Rng2.Columns(1), "<=" & LastYrEnddate)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'for info only during testing, delete when satisfied
'MsgBox "Last years distance as at " & LastYrEnddate1 & " was " & LastYrDist & vbLf & _
'       "This years distance is " & ThisYrDist, , "For Info Only"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' the message box
If ws1.Cells(lr2, 3).Value > 0 Then
    If ThisYrDist > LastYrDist And ThisYrDist <= LastYrDist + 10 Then
        MsgBox "You have run more miles this year than last, as at " & LastYrEnddate
    End If
End If

As the total miles for 2021 is already greater than 2020, I increased the value in one of the cells for 2020 so the total would be within the critical range. I then entered a value of 5 in the last row of Col C so the 2020 total would be exceeded within the range and the msgbox would then be triggered, but I'm afraid nothing happened.

I also reduced the number of non "REST" and "OTHER" entries for 2021 so it equalled 2020 and then entered a test entry to trigger the "number of runs" macro. Again, nothing I'm afraid.

I disabled other sheet_change event macros in case they were interfering and repeated the above, but still no msgboxes.

Would you like me to paste the other code in the worksheet_change event? How else can I help? I can upload the workbook with only this sheet code and in a different format if that would help?

Thanks again.

 

[NoSparks]

Upload the workbook, with all code and data adjustments you have made so that I can just open the workbook, change the route and the mileage in the last record of the "Training Log" sheet to see what happens, thanks

 

[Ironman]

Sure - and thank you again.

The attached is the up to date workbook, without workbook_open event code, as you had trouble opening it.

I tested your code in a test workbook, so your above code isn't included, I hope that's OK.

Here

 

[NoSparks]

If you uncomment the info message boxes, do the numbers they give make sense and would they satisfy the If(s) for the other message boxes to appear?

Edit: added this
running the stand alone macros on the workbook you've just posted
for macro 1
the count is ok, 54 and 56, but the message box won't display because the last record isn't a route
for macro 2
the mileage info differs by 164.4 miles which is a greater than the 10 you asked for.

 

[Ironman]

When I uncommented the test message boxes, the numbers were correct and made sense (I know they were only for test purposes, but just to let you know that the 2 messages appeared each time I entered a value in the next column in the row).

When your code was running, the other code in the change_event was also running and your intended message boxes ("You have run more miles/times this year" etc) didn't run. I then commented out all the other code in the change_event, assuming some of the other code was interfering, but it made no difference, as described above.

I hope that answers your question?

Edit:
Macro 1: Understood, but when I tested your code I added a new entry so the latest entry would be a route. ***BTW, 56 is 2 greater, not less than 2 i.e. 1 greater so it shouldn't trigger?***
Macro 2: Understood, 10 remains the requirement. When I tested your code I added 164 miles to 2020 and then added an entry in the last row to trigger the message box when it was greater by less than 10 miles.

 

[NoSparks]

Try these two macros as stand alones only.
Let me know if they work or
if they don't work, tell me which IF line is not satisfied enroute to the message box.

Sub For_Macro1_v2()
    Dim ws As Worksheet
    Dim lr As Long, Rng As Range
    Dim ThisYrStartdate As Date, ThisYrEnddate As Date
    Dim LastYrStartdate As Date, LastYrEnddate As Date
    Dim ThisYrCount As Long
    Dim LastYrCount As Long
    
' sheet to work on
Set ws = Sheets("Training Log")
' range to work with
With ws
    lr = .Range("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Rng = .Range("A12:C" & lr)
End With
' dates to work with
ThisYrStartdate = DateSerial(Year(Date), 1, 1)
ThisYrEnddate = Date
LastYrStartdate = DateAdd("yyyy", -1, ThisYrStartdate)
LastYrEnddate = DateAdd("yyyy", -1, Date)
' calculation of thisyrcount
ThisYrCount = Application.WorksheetFunction.CountIfs(Rng.Columns(3), ">0", Rng.Columns(1), ">=" & ThisYrStartdate)
' calculation of lastyrcount
LastYrCount = Application.WorksheetFunction.CountIfs(Rng.Columns(3), ">0", Rng.Columns(1), ">=" & LastYrStartdate, Rng.Columns(1), "<=" & LastYrEnddate)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'for info only during testing, delete when satisfied
MsgBox "Last years count as at " & LastYrEnddate & " was " & LastYrCount & vbLf & _
       "This years count is " & ThisYrCount, , "For Info Only"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Stop    'USE THE F8 KEY FROM HERE TO END SUB

' the message box
With ws.Cells(lr, 2)
    If Left(.Value, 4) <> "REST" Then
        If Left(.Value, 5) <> "OTHER" Then
            If ThisYrCount > LastYrCount Then
                If ThisYrCount <= LastYrCount + 2 Then
                    MsgBox "You have been running more times this year than last, as at " & LastYrEnddate
                End If
            End If
        End If
    End If
End With
End Sub

Sub For_Macro2_v2()
    Dim ws As Worksheet
    Dim lr As Long, Rng As Range
    Dim ThisYrStartdate As Date, ThisYrEnddate As Date
    Dim LastYrStartdate As Date, LastYrEnddate As Date
    Dim ThisYrDist As Single
    Dim LastYrDist As Single

Application.ScreenUpdating = False
' sheet to work on
Set ws = Sheets("Training Log")
' range to work with
With ws
    lr = .Range("A:C").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set Rng = .Range("A12:C" & lr)
End With
' dates to work with
ThisYrStartdate = DateSerial(Year(Date), 1, 1)
ThisYrEnddate = Date
LastYrStartdate = DateAdd("yyyy", -1, ThisYrStartdate)
LastYrEnddate = DateAdd("yyyy", -1, Date)
' calculation of thisyrdist
ThisYrDist = Application.WorksheetFunction.SumIfs(Rng.Columns(3), Rng.Columns(3), ">0", Rng.Columns(1), ">=" & ThisYrStartdate)
' calculation of lastyrcount
LastYrDist = Application.WorksheetFunction.SumIfs(Rng.Columns(3), Rng.Columns(3), ">0", Rng.Columns(1), ">=" & LastYrStartdate, Rng.Columns(1), "<=" & LastYrEnddate)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'for info only during testing, delete when satisfied
MsgBox "Last years distance as at " & LastYrEnddate & " was " & LastYrDist & vbLf & _
       "This years distance is " & ThisYrDist, , "For Info Only"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Stop    'USE THE F8 KEY FROM HERE TO END SUB

' the message box
If ws.Cells(lr, 3).Value > 0 Then
    If ThisYrDist > LastYrDist Then
        If ThisYrDist <= LastYrDist + 10 Then
            MsgBox "You have run more miles this year than last, as at " & LastYrEnddate
        End If
    End If
End If
End Sub

 

[Ironman]

Hi Nolan

Macro 1 - The number count for last year in the test message is shown as 48 but it's actually 54 (the last row the code counted was 8207 for June 9 2020 - you'll see this when you check the workbook), so I deleted 7 entries from this year, to reduce the number for this year to 49 to trigger the message box. The good news is when I keyed F8 line by line I got the message box, so that one works!

Macro 2 - Similarly, the mileage sum for last year in the test message is also incorrect at 326.9 (the last row the code summed was also 8207 for June 9 2020) and it should be 347. I therefore increased last year's mileage to between 327 and 337 (making sure to use an entry before 9 June so it would be included) so it would trigger the message box. Unfortunately, no message box with this one though. I keyed F8 line by line and each line highlighted yellow, just like Macro 1, the only difference was no message box.