Countifs VBA to count non blank cells

1
votes

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?

2
vbaexcel
Ever since Excel 2007 there is a function called COUNTBLANK. Is that of any help? support.office.com/en-us/article/… - Ralph
No, because that's not the only condition that needs to be met - N. Pavon
Apparently, the COUNTIF function supports wildcards: support.office.com/en-gb/article/… There is also an example on the site which allows you to Counts the number of cells containing any text[...]. To ensure that at least one character is in a cell you can even look for the following wildcard combination: *?*. - Ralph

2 Answers

2
votes

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)
1
votes

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