Excel VBA - Simpler way to loop through multiple variables of data

Question

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:

CACsatSummary.Range("B2") = WorksheetFunction.CountIfs(name, var01, score, "1")

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.

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.

Any help is appreciated.

'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")

Answer 1

I have used a select case to change the analysts names and two for loops to cycle through the different rows and columns. You could swap out the Select Case for the Array from Vityata's answer. and then just increment the array with the variable i (mypeople(i))

    Dim CACsatSummary As Worksheet, CACsatDetails As Worksheet, name As Range, score As Range
    Set CACsatSummary = Sheets("CSAT Summary")
    Set CACsatDetails = Sheets("CSAT Details")
    Set Names = Sheets("CSAT Details").Range("C2:C10000")
    Set score = Sheets("CSAT Details").Range("D2:D10000")

'Find the values
For x = 1 To 5
   For i = 1 To 7
    Select Case i
    Case 1
    varN = "A,  Person"
       Case 2
    varN = "B,  Person"
        Case 3
    varN = "C,  Person"
        Case 4
    varN = "D,  Person"
        Case 5
    varN = "E,  Person"
        Case 6
    varN = "F,  Person"
        Case 7
    varN = "G,  Person"
    End Select
CACsatSummary.Cells(i + 1, x + 1) = WorksheetFunction.CountIfs(Names, varN, score, x)
  Next i
  Next x

Answer 2

The problem that you have is to loop correctly. And to control this loop. In general, you have to loop through columns and rows in Excel and to give them some value from a worksheetfunction, which contains some variables. To make it easier, I will write the var01, var02 ... var07 to an Array() called myPeople and I will loop through them, assigning each one to a cell.

It is better seen & tested than explained:

Sub TestMe()

    Dim myPeople    As Variant
    Dim cntRows     As Long
    Dim cntCols     As Long
    Dim cntArr      As Long        

    myPeople = Array("A", "B", "C", "D", "E", "F", "G")

    For cntCols = 2 To 7        'column B to G
        For cntRows = 2 To 8    'row 2 to row 8
            With Worksheets(1)
                .Cells(cntRows, cntCols) = myPeople(cntArr Mod UBound(myPeople))
            End With
            cntArr = cntArr + 1
        Next cntRows
    Next cntCols

End Sub

The tricky part in your task is to find a way to loop through the array of myPeople and once you go to its end to start from the beginning. This can easily be achieved through the Mod function + an incremented counter. Thus, at the end of the code you will achieve this:

Which is not quite what you wanted, but it has the answer of the two main tasks:

• how to loop through cells
• how to loop through array

From there to your WorksheetFunction.CountIfs the way should be clear, if you give it a try for about 30 minutes.