Trouble with COUNTIFS function on cell values obtained through VBA

[masud8956]

Hi,

I am trying to use the following formula to count no of 'date' entries within a specific range between two dates at E7 and E8.

=COUNTIFS($F$8:$F$107,">="&$E$7,$F$8:$F$107,"<="&$E$8)

The values within the range F8:F107 are dates (stamped through a VBA).

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Chng As Range, Cl As Range
   
   Set Chng = Intersect(Target, Range("F8:F107,K8:K107,O8:O107,S8:S107,W8:W107"))
   If Not Chng Is Nothing Then
      Me.Unprotect "password"
      For Each Cl In Chng
         If Cl.Value = "" Then
            Cl.Offset(, -1).Value = ""
         Else
            Cl.Offset(, -1).Value = Format(Date, "dd mmm yy (ddd)")
         End If
      Next Cl
      Me.Protect "password"
   End If
End Sub

The COUNIFS formula returns zero value. But it works fine on a range which is not populated through VBA code.

How can I get the formula work using data from F, K, O, S and W columns?

Thanks

 

[RoryA]

You are putting text into the cells, not a true date. You should format the cells, not the text:

       Private Sub Worksheet_Change(ByVal Target As Range)
Dim Chng As Range, Cl As Range

Set Chng = Intersect(Target, Range("F8:F107,K8:K107,O8:O107,S8:S107,W8:W107"))
If Not Chng Is Nothing Then
Me.Unprotect "password"
For Each Cl In Chng
If Cl.Value = "" Then
Cl.Offset(, -1).Value = ""
Else
with Cl.Offset(, -1)
    .Value = Date
    .Numberformat = "dd mmm yy (ddd)"
end with
End If
Next Cl
Me.Protect "password"
End If
End Sub

 

[masud8956]

Works fine with the modified code. Thanks a lot!

 

[RoryA]

Glad to help.