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.Transpos e(Workshee tFunction. Transpose( Sheets(1). Range("B7" ).Resize(L astRow, 8).Value))
cnd1 = Sheets(1).Range("F2").Valu e
cnd2 = Sheets(1).Range("G2").Valu e
cnd3 = Sheets(1).Range("H2").Valu e
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
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).
vArray1 = WorksheetFunction.Transpos
cnd1 = Sheets(1).Range("F2").Valu
cnd2 = Sheets(1).Range("G2").Valu
cnd3 = Sheets(1).Range("H2").Valu
dt1 = Format(Sheets(1).Range("L2
dt1 = Format(Sheets(1).Range("L3
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
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.
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
C--Users-Jamil-Downloads-Trial-Count.xls