[EXCEL] Using CountIf in VBA

[mekinnik]

I am looking for some help fixing my counting problem. I am trying to count the number of rows that meet 2 criteria. The CountIf is using 2 different labels as the criteria. Here is what I have so far.

Private Sub CmdBtn_Click()
'Perform search of column 'D' and return
'results to LblNum

Dim num As Integer

ActiveSheet.Unprotect

num = Application.CountIf(Sheets("Sheet1").Range("D:D", LblDate1) - Application.CountIf(Sheets("Sheet1").Range("D:D", LblDate2)))

LblNum = num

ActiveSheet.Protect

End Sub

[mekinnik]

If figured it out myself.

Private Sub CmdBtn_Click()
'Perform search of column 'D' and return
'results to LblNum

Dim num As Integer
Dim date1 As String, date2 As String

ActiveSheet.Unprotect

date1 = LblDate1.Caption
date2 = LblDate2.Caption

If date1 = date2 Then
    num = Application.CountIf(Columns("D:D"), date1)

Else
    num = Application.CountIf(Columns("D:D"), date1) + Application.CountIf(Columns("D:D"), date2)

End If

LblNum = num

ActiveSheet.Protect
End Sub

[Siddharth Rout]

The code might fail under some circumstances. Would suggest this small change... See the code in red...

Private Sub CmdBtn_Click()
    Dim num As Integer, date1 As String, date2 As String

    ActiveSheet.Unprotect

    date1 = Trim$(LblDate1.Caption)
    date2 = Trim$(LblDate2.Caption)

    If date1 = date2 Then
        num = Application.CountIf(Columns("D:D"), date1)
    Else
        num = Application.CountIf(Columns("D:D"), date1) + _
        Application.CountIf(Columns("D:D"), date2)
    End If

    LblNum = num

    ActiveSheet.Protect
End Sub