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

maybe use pivot table. you can create one in vba, too.

PalimPalim
– PalimPalim

2017-12-19 16:00:57 +00:00
Commented Dec 19, 2017 at 16:00 — PalimPalim
– PalimPalim, Commented Dec 19, 2017 at 16:00

mooseman · Accepted Answer · 2017-12-19 17:18:26Z

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

Vityata · Accepted Answer · 2017-12-19 16:15:14Z

3

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.

edited Dec 19, 2017 at 16:15

answered Dec 19, 2017 at 16:09

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

4 Comments

sbagnato Over a year ago

So, if I have a table with (let's say) 1000 rows of data. Each person is in there in various locations, with various scores. The summary table (like the one you pasted above) has column headers of 1-5, and row labels of A-G (analyst names). So theoretically, the code you provided should (hopefully) continuously loop through and list the number of instances of each score for each analyst? Just want to make sure I am understanding correctly what you have provided.

Vityata Over a year ago

@sbagnato - just open a new Excel file and give it a try. Worse case scenario you would need to drink a coffee before it is over.

sbagnato Over a year ago

Lol. Well, it "worked", and fast. However, instead of provided the number of instances of each score for each analyst, it just gave an exact replica of the screenshot you provided. So I will have to play with it a little bit to get it to produce the results I was getting before.

Vityata Over a year ago

@sbagnato - good to hear that it worked somehow. In general, you have two changing variables in the WorksheetFunction - var05 etc and 1,2,3. For the latter you may use a counter on the outer loop, it will work. Good luck, have fun! :)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related