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
COUNTIF(Range("JA:KD & i"), Sheet2.Cells(j,1) And Sheet4.Cells(i, k) = "TRUE" And Sheet4.Cells(i + 4, k) = "TRUE")