CODEWITHSUNDEEP
vbaexcel
N. Pavon
N. Pavon

Reputation: 839

Countifs VBA to count non blank cells

I have the following VBA formula to count non blank cells in a range, plus some other conditions in other ranges. The part to count non blank cells is not working. This is the piece of code:

LAX(0) = Application.WorksheetFunction.CountIfs(Range("I:I"), "<>""", Range("AH:AH"), "LAX", Range("AG:AG"), ">=" & semanaI, Range("AG:AG"), "<=" & semanaF)

Are the first two arguments correct?

Upvotes: 1

Views: 17865

Answers (2)

grug.0
grug.0

Reputation: 355

Unfortunately you need a little bit more handling with your conditional, because If a cell in any argument is an empty cell, CountIfs treats it as a 0 value.

(REFERENCE, BRO: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheetfunction.countifs.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1)

So,

Application.WorksheetFunction.CountIfs( {range to evaluate criteria}, {criteria_1} ,{criteria_2},...)

do this:

Dim rCell as Range.Cells
Dim rRange as range

Set rRange = Range("I:I")

For Each rCell in rRange

    If { foo } Then
        'do stuff
    Elif { bar} 
        'do stuff
    Else { derp}
        'do stuff
    End If

Debug.Print rCell.Address, rCell.Value

Next rCell

Upvotes: 1

N. Pavon
N. Pavon

Reputation: 839

I solved it by doing:

LAX(0) = Application.WorksheetFunction.CountIfs(Range("I:I"), "<>" & "", Range("AH:AH"), "LAX", Range("AG:AG"), ">=" & semanaI, Range("AG:AG"), "<=" & semanaF)

Upvotes: 3

Related Questions