Message box counting total for current date

[cns324]

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.

 

[Peter_SSs]

Welcome to the MrExcel board!

Give this a try

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

 

[cns324]

Perfect!

 

[Joe4]

This can actually be done even simpler, with a single line of code and no loops needed:

Sub CountToday()
    MsgBox Application.WorksheetFunction.CountIf(Range("A:A"), Date)
End Sub

 

[Peter_SSs]

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
1	25/01/2022
2	52361
3	25/01/2022
4	44586
5	25/01/2022
6	43555
7	25/01/2022
8	18/05/2012
9
10	xxx
11
12	25/01/2022
Sheet1

Cell Formulas
Range	Formula
A1,A12,A7,A5,A3	A1	=TODAY()

 

[cns324]

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]

Loops are not necessary. You can use the logic I showed you above, i.e.

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.

 

[cns324]

Works great. Thanks

 

[Joe4]

You are welcome.