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
Solved

Excel VBA Countifs not Working

Posted on 2014-10-03
7
207 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 26

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 26

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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to make a Windows 7 gadget that accepts files dropped from the Windows Explorer.  It also illustrates how to give your gadget a non-rectangular shape and how to add some nifty visual effects to text displayed in a your gadget.…
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

790 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