CODEWITHSUNDEEP
excelvba
JoshSouthern
JoshSouthern

Reputation: 43

Optimising a For Each loop used for counting Excel VBA

I'm currently working on a Quality Concern log that will be used at my place of work to track the quality concerns, and output specific data to the management via a dashboard.

One of the calculations i have, go through rows in a log and counts the number of rows that meet a certain criteria. It is essentially a CountIf function, but with a For loop. The count is then dumped into a cell, and the calculation moves onto the next value in the range.

I've currently got 95 entries into the log and the counts are running pretty slowly. As we get more quality concerns, its inevitable that the code will start to run even slower.

This is a sample of the code i'm running:

For Each cell In mnthRng

    monthVal = cell.value
    YearVal = cell.Offset(-1, 0).value
    num = 1
    Total_prjctCount = 0
    
    For i = LBound(prjcts) To UBound(prjcts)
        PrjctName = prjcts(i)
        included_in_calcs = prjctYesNo(num, 1)
        If included_in_calcs = "YES" Then
            Total_Count = 0
            For j = 8 To IDLastRow
                If QCRLogSheet.Range("AI" & j) = monthVal _
                And QCRLogSheet.Range("AK" & j) = YearVal _
                And QCRLogSheet.Range("D" & j) = PrjctName Then
                    Total_Count = Total_Count + 1
                Else
                End If
            Next j
                
            Total_prjctCount = Total_Count + Total_prjctCount
        End If
        num = num + 1
    Next i
    cell.Offset(1, 0).value = Total_prjctCount
Next cell

Just to give you some more information on the code:

mnthRng is a cell range containing different months.

The array prjcts contains the name of the various different projects we have onsite, and allows me to sort the data out by project is someone unticks the "include in calculations" box on the dashboard

I've read that to speed up calculations of this nature, instead of looping per cell, i could add the range to an array, and do the count in the array. Unfortunately i'm not sure how i go about adding my data range into an array and then looping through it.

Any help would be much appreciated!

Upvotes: 1

Views: 41

Answers (1)

Tim Williams
Tim Williams

Reputation: 166341

Untested:

Dim arrMonth, arrYear, arrProj

arrMonth = QCRLogSheet.Range("AI8:AI" & IDLastRow)
arrYear = QCRLogSheet.Range("AK8:AK" & IDLastRow)
arrProj = QCRLogSheet.Range("D8:D" & IDLastRow)

For Each cell In mnthRng

    monthVal = cell.Value
    YearVal = cell.Offset(-1, 0).Value
    num = 1
    Total_prjctCount = 0
    
    For i = LBound(prjcts) To UBound(prjcts)
        PrjctName = prjcts(i)
        included_in_calcs = prjctYesNo(num, 1)
        If included_in_calcs = "YES" Then
            Total_Count = 0
            For j = 1 To UBound(arrMonth, 1)
                'nested if's are faster...
                If arrMonth(j, 1) = monthVal Then
                    If arrYear(j, 1) = YearVal Then
                        If arrProj(j, 1) = PrjctName Then Total_Count = Total_Count + 1
                    End If
                End If
            Next j
                
            Total_prjctCount = Total_Count + Total_prjctCount
        End If
        num = num + 1
    Next i
    cell.Offset(1, 0).Value = Total_prjctCount
Next cell

Upvotes: 1

Related Questions