Worksheetfunction.Countifs question

[Pyrgos]

Hello there,

I have been trying to make the below code work, but I can't seem to make it work

With shCAP
                        Dim Result As Integer, b As Long, theDate as Date
                        Dim rngAssociates As Range, rngDates As Range, Associate as Range
                        b = lastRow(shCAP) 'retrieves last row of the sheet
                        Set Associate = shMain.Range("B" & Selection.Row)                        
                        Set rngAssociates = .Range("A2:A" & b)
                        Set rngDates = .Range("C2:C" & b)
                        theDate = shMain.Range("C" & Associate.Row)
                        Result = WorksheetFunction.CountIfs(rngAssociates, Associate, _
                          rngDates, ">=" & theDate, rngDates, "<=" & DateAdd("m", 1, theDate) - 1)
                          Debug.Print Result & " " & Associate & " " & theDate & " "; DateAdd("m", 1, theDate) - 1
                    End With

I have tried Associate.Value. I have tried CDate() on the date ranges. No success.

My debug.print code returns Result as always 0, even though I am sure the columns and sheet are correct, and that there are instances where the count would be more than zero.

Is there anything wrong in my syntax?

 

[theBardd]

I haven't tried it but you could try casting the dates, and is there an extra ASsociate?

cWorksheetFunction.CountIfs(rngAssociates, _
                          rngDates, ">=" & CLng(theDate), rngDates, "<=" & CLng(DateAdd("m", 1, theDate) - 1))

 

[Pyrgos]

Of course! That makes absolute sense. And it worked :D

Thank you very much!