title: Run calculation on Array in VBA - across columns
body: <p>I would like to loop through each row and count the number of non-contiguous columns with a Yes (AQ, AS, AU,CI, etc). The total count would populate into a separate cell(CL).</p>
  <p><a href="https://i.stack.imgur.com/CHmM0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CHmM0.png" alt="enter image description here" /></a></p>
  <p>I think the array is storing the data correctly, but I am not able to accomplish the correct count within a row.</p>
  <pre><code>Sub DynaAtLeastOneSchoolGoalColumnYN()
  Dim R As Long, C As Long, J As Long
  Dim eNumStorage() As Variant
  Dim lrow As Long
  
  
  With Worksheets(&quot;School EOY Data&quot;)
  lrow = .Cells(Rows.Count, 3).End(xlUp).Row
  ReDim eNumStorage(0 - J)
  
      For R = 3 To 4 'The number of rows in the sheet
          For C = 43 To 87 ' The columns to include
              If .Cells(R, C).Value = &quot;Yes&quot; Then
              For J = LBound(eNumStorage) To UBound(eNumStorage)
                  eNumStorage(J) = .Cells(R, C).Value
                  Debug.Print eNumStorage(J) &amp; &quot; &quot; &amp; .Cells(R, C).Value &amp; &quot; &quot; &amp; .Cells(1, C).Value &amp; &quot; r = &quot; &amp; R ' this prints all of the columns with a Yes that should be stored in the array.
                  
              Next J
              Else
                  End If
              C = C + 1
          For J = LBound(eNumStorage) To UBound(eNumStorage)
              eNumStorage(J) = Application.WorksheetFunction.CountA(eNumStorage(J)) 'count all of the values in the array for this row
              'Debug.Print eNumStorage(J) ' would like to print the value 2 for row 3, and the value 1 for row 4
          Next J
              
          Next C
  
      Next R
  
  End With
  
  End Sub
  </code></pre>