VBA, Get count from each sheet using multiple criteria

Question

I have 10 sheets in my workbook and the 10th sheet contains names of the first 9 sheets. I was trying to create a FORMULA using VBA to get the count from each sheet.

For example, I have data in sheet1 from range (a1:b500) and the criteria for the count is for every non-blank cell in the Column A there should be a blank cell in Column B.

Is it possible to create a UDF which takes range (a1:a500) as one array and range (b1:b500) as the second array and give the count based on the above criteria? The argument in the function should be only the sheet name – For instance, the function should take the argument as =GetCount(sheet1), where GetCount is the function name and sheet1 is the argument.

I found the solution using loop, but I want to avoid using loop in vba, countif and sum(array) in Excel and tryout with array feature

Function GetCount(Str As String)
    Application.Volatile (True)
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim rng1 As Range
    Dim r As Integer
    Dim strCount As Integer
        Set wb = Workbooks("Integrated Working Tracker")
        Set sh = wb.Sheets(Str)
        Set rng1 = sh.Range("a1:a500")
        For Each c In rng1.Cells
         If c.Value <> "" And c.Offset(0, 1).Value = "" Then
         strCount = strCount + 1
         End If
        Next
    GetCount = strCount
    Set sh = Nothing
    Set wb = Nothing
    Set rng1 = Nothing
End Function

Any help is much appreciated. Thank you in advance

I thing that you can accomplish that with conditional formatting, there's no need for VBA code!!! Have you tried that? — Sylca

KekuSemau KekuSemau · Accepted Answer · 2013-09-08T09:48:35

You can do this with a sumproduct formula.

In a cell itself, the formula would work like this:

=SUMPRODUCT((A1:A500<>"")*(B1:B500=""))

The individual conditional expressions, if combined in calculations, are converted to 1 or 0, and the row-wise results are then summed up.

In VBA, there is Worksheetfunction.SumProduct, but (as far as I remember, didn't test this now again) this does not support this 'specific' functionality (=converting boolean expressions to 1 or 0). So you have to use Evaluate.

Function GetCount(shName As String)
    Dim wb As Workbook
    Dim sh As Worksheet

    Set wb = Workbooks("Integrated Working Tracker")
    Set sh = wb.Worksheets(shName)
    GetCount = sh.Evaluate("SUMPRODUCT((A1:A500<>"""")*(B1:B500=""""))")
End Function

Edit: Since Str is also an integrated function, I would not use it as a variable name.

VBA, Get count from each sheet using multiple criteria

1 Answers