Searching for a range of dates in Excel Using VBA

The procedure finds the last populated, *-th column in the 4th row and iterates through all cells in the range F4:*4, checking whether the date is within the given range and if the x is present in the corresponding cell in the 5th row, and updates the counter in A2.

Sub countDates()

Dim L As Long

L = ActiveSheet.Cells(4, ActiveSheet.Columns.Count).End(xlToRight).Column

Cells(2, 1).Value = 0
For x = 6 To L
If Cells(4, x).Value >= Cells(5, 4).Value And Cells(4, x).Value <= Cells(5, 5).Value And Cells(5, x) = "x" Then Cells(2, 1).Value = Cells(2, 1).Value + 1
Next

End Sub

Below is another, more elaborate solution that uses Range.Find method. This is quite inefficient and more verbose, but I am posting to reply to OP (under deleted answers). Furthermore, it is known that using the .Find method to search dates is quite unreliable; the issue has been discussed on SO on several occasions.

Anyway, my idea behind this solution is to remove all dates without an x in the 5th row, then count the matches. Counting the matching dates is done by iterating from the starting date to the end date (hardcoded in [D5] and [E5], respectively), incrementing the counter, when a match is found, and removing the matching date. This is done recursively until no more matching dates remain.

The output is saved to [A2].

Even though the original list of dates is restored at the end, it is better to use a backup file.

Option Explicit
Public L As Long

'''''''''''''''''''''
''''' Main call '''''
'''''''''''''''''''''

Sub countDates2()

Dim j As Long

L = ActiveSheet.Cells(4, ActiveSheet.Columns.Count).End(xlToLeft).Column
ReDim dates(L) As Date

For j = 6 To L
 dates(j) = Cells(4, j).Value
 If Cells(5, j).Value = "" Then
  Cells(4, j).Value = ""
 End If
Next

[A2] = findAndKill(0)

For j = 6 To L: Cells(4, j).Value = dates(j): Next

End Sub

'''''''''''''''''''''''''''''''
''''' Function definition '''''
'''''''''''''''''''''''''''''''

Function findAndKill(a As Long) As Long

Dim i, n As Long
Dim c As Range

 For n = [D5] To [E5]
 
 With ActiveSheet.Range(Cells(4, 6), Cells(4, L))
 Set c = .Find(Format(n, "dd.mm.yyyy"), LookIn:=xlValues)

 If Not c Is Nothing Then

   i = i + 1
   c.Value = ""
   
 End If
 
 End With
 Next n
 
 If i > 0 Then
  findAndKill = findAndKill(0) + i
 Else
  findAndKill = 0
End If
 
End Function