Link to home
Start Free TrialLog in
Avatar of DougDodge
DougDodge

asked on

Excel VBA Countifs not Working

The following code just does not seem to work, it probable has to do with date syntax but it baffles me.

Sub myCountBBO2()

Dim counts As Integer, i As Integer
Dim LastRow As Long
Dim vArray1 As Variant
Dim dt1 As Date, dt2 As Date
Dim cnd1 As String, cnd2 As String, cnd3 As String

LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row
vArray1 = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Sheets(1).Range("B7").Resize(LastRow, 8).Value))

cnd1 = Sheets(1).Range("F2").Value
cnd2 = Sheets(1).Range("G2").Value
cnd3 = Sheets(1).Range("H2").Value
dt1 = Format(Sheets(1).Range("L2").Value, "d-mmm-yy")
dt1 = Format(Sheets(1).Range("L3").Value, "d-mmm-yy")

MsgBox dt1

For i = LBound(vArray1) To UBound(vArray1)
    If vArray1(i, 1) = cnd1 And vArray1(i, 2) = cnd2 And vArray1(i, 3) = CInt(cnd3) And vArray1(i, 4) >= dt1 Or vArray1(i, 4) <= dt2 Then
        counts = counts + 1
    End If
Next i

Sheets(1).Range("M2") = counts

End Sub
Trial-Count.xlsm
Avatar of Professor J
Professor J

Avatar of DougDodge

ASKER

No luck, it is exactly the same, nothing has changed..... In fact it is the same file I posted....
Can you upload a sample file that indicates a correct result. So that based on that u can see what is required
I think that your logic is wrong

For i = LBound(vArray1) To UBound(vArray1)
     If vArray1(i, 1) = cnd1 And vArray1(i, 2) = cnd2 And vArray1(i, 3) = CInt(cnd3) And vArray1(i, 4) >= dt1 Or vArray1(i, 4) <= dt2 Then
         counts = counts + 1
     End If
 Next I

do you actually mean

For i = LBound(vArray1) To UBound(vArray1)
     If vArray1(i, 1) = cnd1 And vArray1(i, 2) = cnd2 And vArray1(i, 3) = CInt(cnd3) And (vArray1(i, 4) >= dt1 Or vArray1(i, 4) <= dt2) Then
         counts = counts + 1
     End If
 Next i

Without the brackets if the last bit is true then the expression will be true irrespective of the first parts. With the brackets the first 3 must be true and one of the last two must be true for the expression to be true.
Sorry, it still gives the wrong answer. This is really a confusing problem....
I have attached the same file, if you open it you should see the filtered results of a count of 3, and yet the code gives a count of 0 in cell $M$2.
Maybe an array is not the right approach to this.
Test-of-Countif.xlsm
ASKER CERTIFIED SOLUTION
Avatar of ChloesDad
ChloesDad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You were right, I should have caught that. I was changing so many things trying to get it to work I totally missed it.... Thanks