Message box counting total for current date

cns324

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I am sure this is a simple code, but I have not had any luck yet, and I am new at VBA. I would like a message box that counts the total rows that has today's date. It only needs to look in Column A for the date. But it needs to look for todays current date, i.e. today I want it to look for 1/21/2022, but tomorrow look for 1/22/2022 and give me the count.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,286
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Give this a try

VBA Code:
Sub Count_Today()
  Dim a As Variant
  Dim cToday As Long, i As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If a(i, 1) = Date And IsDate(a(i, 1)) Then cToday = cToday + 1
  Next i
  MsgBox cToday
End Sub
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,089
Office Version
  1. 365
Platform
  1. Windows
This can actually be done even simpler, with a single line of code and no loops needed:
VBA Code:
Sub CountToday()
    MsgBox Application.WorksheetFunction.CountIf(Range("A:A"), Date)
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,286
Office Version
  1. 365
Platform
  1. Windows
I'm sure it would be extremely unlikely - or even impossible - with your data, but it is possible that could give an incorrect result. For example, it returns 6 instead of 5 for me for this data.

cns324.xlsm
A
125/01/2022
252361
325/01/2022
444586
525/01/2022
643555
725/01/2022
818/05/2012
9
10xxx
11
1225/01/2022
Sheet1
Cell Formulas
RangeFormula
A1,A12,A7,A5,A3A1=TODAY()
 

cns324

New Member
Joined
Jan 21, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Could this be updated to count the current date for 2 sheets? Sheet 2 (Service) and Sheet 4 (Review)?

Sub Count_Today()
Dim a As Variant
Dim cToday As Long, i As Long

a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
For i = 1 To UBound(a)
If a(i, 1) = Date And IsDate(a(i, 1)) Then cToday = cToday + 1
Next i
MsgBox cToday
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,089
Office Version
  1. 365
Platform
  1. Windows
Loops are not necessary. You can use the logic I showed you above, i.e.
VBA Code:
Sub CountToday()
    MsgBox Application.WorksheetFunction.CountIf(Sheets("Service").Range("A:A"), Date) + Application.WorksheetFunction.CountIf(Sheets("Review").Range("A:A"), Date)
End Sub
Loops should be avoided when other alternatives exist. They are generally slow and inefficient.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,089
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,181,220
Messages
5,928,753
Members
436,628
Latest member
Krakenfan69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top