title: VBA, Get count from each sheet using multiple criteria
body: <p>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.</p>
  
  <p>For example, I have data in <code>sheet1</code> 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. </p>
  
  <p>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 <code>=GetCount(sheet1)</code>, where <code>GetCount</code> is the function name and sheet1 is the argument.</p>
  
  <p>I found the solution using loop, but I want to avoid using loop in vba, <code>countif</code> and <code>sum(array)</code> in Excel and tryout with array feature</p>
  
  <pre><code>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 &lt;&gt; "" 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
  </code></pre>
  
  <p>Any help is much appreciated. Thank you in advance</p>