Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA Countifs not Working

Posted on 2014-10-03
7
Medium Priority
?
223 Views
Last Modified: 2014-10-04
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
0
Comment
Question by:DougDodge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40360594
0
 

Author Comment

by:DougDodge
ID: 40360782
No luck, it is exactly the same, nothing has changed..... In fact it is the same file I posted....
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40360947
Can you upload a sample file that indicates a correct result. So that based on that u can see what is required
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 15

Expert Comment

by:ChloesDad
ID: 40360978
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
 

Author Comment

by:DougDodge
ID: 40361198
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
 
LVL 15

Accepted Solution

by:
ChloesDad earned 2000 total points
ID: 40361221
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
 

Author Closing Comment

by:DougDodge
ID: 40361247
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question