CODEWITHSUNDEEP
vbaexcelcountif
user1982557
user1982557

Reputation: 11

VBA: Complicated CountIf

I'm trying to implement a CountIf UDF in VBA with multiple AND arguments. I have constructed the following code which gives me an app-defined or obj error. 

Sub Count_PoA()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row - 5
    End With

    For i = 6 To LastRow
        Count = 0
        For j = 17 To 58
            For k = 12 To 397 Step 13
                If Sheet9.Range("JA:KD & i") = Sheet2.Cells(1, j) And Sheet4.Cells(i, k) = "TRUE" And Sheet4.Cells(i + 4, k) = "TRUE" Then
                    Count = Count + 1
                End If
                Count = Sheet2.Range("B" & j)
            Next
        Next
    Next
End Sub

Ideally I'd like something equivalent to:

COUNIF(Dim someRange as Range, condition1, condition2, condtion3, ...) Where condition1 can be a value or expression and all the conditions are join together in AND condition

Example: COUNTIF(Range("JA:KD & i"), Sheet2.Cells(j,1) And Sheet4.Cells(i, k) = "TRUE" And Sheet4.Cells(i + 4, k) = "TRUE")

Upvotes: 1

Views: 2630

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Here is something you could try: I have taken my own sample data since we don't see your sheet. Most ranges are hardcoded, but you can change it and make it dynamic accordingly. The arithmetic operator is one thing you need to change manually e.g. <, >, =

Option Explicit

Sub myCountIFS()
Dim counts As Integer, i As Integer
Dim LastRow As Long
Dim vArray1 As Variant
Dim cnd1 As String, cnd2 As String, cnd3 As String

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

cnd1 = Sheets(1).Range("F2").Value
cnd2 = Sheets(1).Range("G2").Value
cnd3 = Sheets(1).Range("H2").Value

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

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

End Sub

Output:

enter image description here

Upvotes: 2

Related Questions