title: Excel VBA - Simpler way to loop through multiple variables of data
body: <p>Interesting question that may not have an answer other than "no".  I have the below code that fills out a spreadsheet.  Explaining this one line:</p>
  
  <pre><code>CACsatSummary.Range("B2") = WorksheetFunction.CountIfs(name, var01, score, "1")
  </code></pre>
  
  <p>To the left of the equals is obvious (sheet and range).  To the right is the CountIfs function.  This is followed by "name", which is a range of column C.  Var01-Var07 are 7 analysts names (dim'd and set above what I have provided) which exist in column C. "score" is another range, this time of column D.  And "1" (also, 2, 3, 4 and 5) is the value that exists in column D.</p>
  
  <p>What the entire code does is loop through a table of data, and count the number of instances of each score (1-5) for each analysts (var01 - var07), and places them into a new table which summarizes all of the data.  While the below code works just fine, I feel like there is probably a better way to write it, instead of the current 40+ lines.</p>
  
  <p>Any help is appreciated.</p>
  
  <pre><code>'Fill in data
      Dim var01, var02, var03, var04, var05, var06, var07 As String
      var01 = "A,  Person"
      var02 = "B,  Person"
      var03 = "C,  Person"
      var04 = "D,  Person"
      var05 = "E,  Person"
      var06 = "F,  Person"
      var07 = "G,  Person"
  
      Dim CACsatSummary As Worksheet, CACsatDetails As Worksheet, name As Range, score As Range
      Set CACsatSummary = Sheets("CSAT Summary")
      Set CACsatDetails = Sheets("CSAT Details")
      Set name = Sheets("CSAT Details").Range("C2:C10000")
      Set score = Sheets("CSAT Details").Range("D2:D10000")
  
  'Find the values
      CACsatSummary.Range("B2") = WorksheetFunction.CountIfs(name, var01, score, "1")
      CACsatSummary.Range("B3") = WorksheetFunction.CountIfs(name, var02, score, "1")
      CACsatSummary.Range("B4") = WorksheetFunction.CountIfs(name, var03, score, "1")
      CACsatSummary.Range("B5") = WorksheetFunction.CountIfs(name, var04, score, "1")
      CACsatSummary.Range("B6") = WorksheetFunction.CountIfs(name, var05, score, "1")
      CACsatSummary.Range("B7") = WorksheetFunction.CountIfs(name, var06, score, "1")
      CACsatSummary.Range("B8") = WorksheetFunction.CountIfs(name, var07, score, "1")
      CACsatSummary.Range("C2") = WorksheetFunction.CountIfs(name, var01, score, "2")
      CACsatSummary.Range("C3") = WorksheetFunction.CountIfs(name, var02, score, "2")
      CACsatSummary.Range("C4") = WorksheetFunction.CountIfs(name, var03, score, "2")
      CACsatSummary.Range("C5") = WorksheetFunction.CountIfs(name, var04, score, "2")
      CACsatSummary.Range("C6") = WorksheetFunction.CountIfs(name, var05, score, "2")
      CACsatSummary.Range("C7") = WorksheetFunction.CountIfs(name, var06, score, "2")
      CACsatSummary.Range("C8") = WorksheetFunction.CountIfs(name, var07, score, "2")
      CACsatSummary.Range("D2") = WorksheetFunction.CountIfs(name, var01, score, "3")
      CACsatSummary.Range("D3") = WorksheetFunction.CountIfs(name, var02, score, "3")
      CACsatSummary.Range("D4") = WorksheetFunction.CountIfs(name, var03, score, "3")
      CACsatSummary.Range("D5") = WorksheetFunction.CountIfs(name, var04, score, "3")
      CACsatSummary.Range("D6") = WorksheetFunction.CountIfs(name, var05, score, "3")
      CACsatSummary.Range("D7") = WorksheetFunction.CountIfs(name, var06, score, "3")
      CACsatSummary.Range("D8") = WorksheetFunction.CountIfs(name, var07, score, "3")
      CACsatSummary.Range("E2") = WorksheetFunction.CountIfs(name, var01, score, "4")
      CACsatSummary.Range("E3") = WorksheetFunction.CountIfs(name, var02, score, "4")
      CACsatSummary.Range("E4") = WorksheetFunction.CountIfs(name, var03, score, "4")
      CACsatSummary.Range("E5") = WorksheetFunction.CountIfs(name, var04, score, "4")
      CACsatSummary.Range("E6") = WorksheetFunction.CountIfs(name, var05, score, "4")
      CACsatSummary.Range("E7") = WorksheetFunction.CountIfs(name, var06, score, "4")
      CACsatSummary.Range("E8") = WorksheetFunction.CountIfs(name, var07, score, "4")
      CACsatSummary.Range("F2") = WorksheetFunction.CountIfs(name, var01, score, "5")
      CACsatSummary.Range("F3") = WorksheetFunction.CountIfs(name, var02, score, "5")
      CACsatSummary.Range("F4") = WorksheetFunction.CountIfs(name, var03, score, "5")
      CACsatSummary.Range("F5") = WorksheetFunction.CountIfs(name, var04, score, "5")
      CACsatSummary.Range("F6") = WorksheetFunction.CountIfs(name, var05, score, "5")
      CACsatSummary.Range("F7") = WorksheetFunction.CountIfs(name, var06, score, "5")
      CACsatSummary.Range("F8") = WorksheetFunction.CountIfs(name, var07, score, "5")
  </code></pre>