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
DougDodgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
0
DougDodgeAuthor Commented:
No luck, it is exactly the same, nothing has changed..... In fact it is the same file I posted....
0
ProfessorJimJamCommented:
Can you upload a sample file that indicates a correct result. So that based on that u can see what is required
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ChloesDadCommented:
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.
0
DougDodgeAuthor Commented:
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
0
ChloesDadCommented:
There are 2 mistaked

firstly all the conditions should be AND

to check for a date between two dates you need A>= X AND A <= Y

the second is that you are setting dt1 twice, the second should be dt2

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")
dt2 = Format(Sheets(1).Range("L3").Value, "d-mmm-yy")



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 And vArray1(i, 4) <= dt2 Then
        counts = counts + 1
    End If
Next i

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DougDodgeAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.