Hello,
I have created a macro that has 2 subroutines that are taking way to long to run. The sub named Count_Number_Jobs (Engineers) takes about 20 seconds to run on my laptop. The sub named Count_Complexities_Per_Engineer (Engineers) takes 2 minutes 40 seconds to run. There are only around 300 to 500 rows of data.
The Count_Number_Jobs(Engineers) routine cycles through an array of 20 engineers and takes a count of how many jobs have been assigned to them each week over a period of 40 weeks. It takes that count and pastes them in another worksheet named "Chart Data". I autofilter by each engineers name in order to reduce the number of rows needed to cycle through. But in a nutshell the script is compiling data points for 20 engineers over 40 weeks. So the processed data would look like this:
<tbody>
</tbody>
Bill has 2 jobs assigned at 6 weeks and 1 at week 7 and so on.
The Count_Complexities_Per_Engineer (Engineers) routine is a little more involved which is why it is taking much longer to run. This routine cycles through the 20 engineers for a 40 week period like the previous routine. However, in each week it is finding the count of complexity values that range from 1 to 10.
So the processed data would look like this:
<tbody>
</tbody>
At -5 weeks Jason has 3 jobs at complexity value 1, 1 job at complexity value 2, 3 jobs at complexity value 4 and 1 job at complexity 9.
I'm sure there is a better way to go about what I am doing. I just don't know what that looks like! I know there is a lot of overhead with looping through data but I don't know of a way around it in the situation that I have.
I have created a macro that has 2 subroutines that are taking way to long to run. The sub named Count_Number_Jobs (Engineers) takes about 20 seconds to run on my laptop. The sub named Count_Complexities_Per_Engineer (Engineers) takes 2 minutes 40 seconds to run. There are only around 300 to 500 rows of data.
The Count_Number_Jobs(Engineers) routine cycles through an array of 20 engineers and takes a count of how many jobs have been assigned to them each week over a period of 40 weeks. It takes that count and pastes them in another worksheet named "Chart Data". I autofilter by each engineers name in order to reduce the number of rows needed to cycle through. But in a nutshell the script is compiling data points for 20 engineers over 40 weeks. So the processed data would look like this:
| Engineer | Week -5 | Week -4 | Week -3 | Week -2 | Week -1 | Week 0 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 |
| Bill | 2 | 1 | 1 | |||||||||||||||
| Jason | 1 | 1 | 3 | |||||||||||||||
| Sue | 1 | 1 | ||||||||||||||||
| Nancy | 2 |
<tbody>
</tbody>
Bill has 2 jobs assigned at 6 weeks and 1 at week 7 and so on.
Code:
Sub Count_Number_Jobs(Engineers As Variant)
Application.StatusBar = "Process 7 of 8 Begin"
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
Dim weekly_job_count As String
Dim job_count_array() As Variant
Dim Z As Long
Dim j As Long
Z = 1
For Each Engineer In Engineers
Worksheets("Raw Data").Range("K1").AutoFilter Field:=11, Criteria1:=Engineer
For i = -5 To 35
With Application
Set Name = Worksheets("Raw Data").Columns("K")
Set Weeks_Out = Worksheets("Raw Data").Columns("AC")
weekly_job_count = .WorksheetFunction.CountIfs(Name, Engineer, Weeks_Out, i) 'name to test is the engineer and i is the week
If weekly_job_count = 0 Then
weekly_job_count = vbNullString
End If
End With
ReDim Preserve job_count_array(j)
job_count_array(j) = weekly_job_count
j = j + 1
Next
j = 0
Z = Z + 1
Worksheets("Chart Data").Range("B" & Z & ":AP" & Z) = job_count_array() 'Paste in the job count for each engineer/each week in the chart data page
Next
Worksheets("Raw Data").ShowAllData
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran in " & SecondsElapsed & " Seconds", vbInformation
Application.StatusBar = "Process 7 of 8 Complete"
End SubThe Count_Complexities_Per_Engineer (Engineers) routine is a little more involved which is why it is taking much longer to run. This routine cycles through the 20 engineers for a 40 week period like the previous routine. However, in each week it is finding the count of complexity values that range from 1 to 10.
So the processed data would look like this:
| Engineer | Week -5 | Week -4 | Week -3 | |||||||||||||||||||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
| Bill | ||||||||||||||||||||||||||||||
| Jason | 3 | 1 | 3 | 1 | 1 | 1 | 3 | 1 |
<tbody>
</tbody>
At -5 weeks Jason has 3 jobs at complexity value 1, 1 job at complexity value 2, 3 jobs at complexity value 4 and 1 job at complexity 9.
Code:
Sub Count_Complexities_Per_Engineer(Engineers As Variant)
Application.StatusBar = "Process 8 of 8 Begin"
Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer
Dim Complexity_Count As String
Dim complexity_count_array() As Variant
Dim j As Long
Dim l As Long
Dim K As Long
K = 2
For Each Engineer In Engineers
Worksheets("Raw Data").Range("K1").AutoFilter Field:=11, Criteria1:=Engineer
For i = -5 To 35
For l = 1 To 10
With Application
Set Name = Worksheets("Raw Data").Columns("K") 'This is the column with the Engineers name
Set Weeks_Out = Worksheets("Raw Data").Columns("AC") 'This is the column with the earliest start date
Set Complexity_Value = Worksheets("Raw Data").Columns("R") 'This is the column with complexity values
Complexity_Count = .WorksheetFunction.CountIfs(Name, Engineer, Weeks_Out, i, Complexity_Value, l)
If Complexity_Count = 0 Then
Complexity_Count = vbNullString
'Complexity_Count = ""
End If
End With
ReDim Preserve complexity_count_array(j)
complexity_count_array(j) = Complexity_Count
j = j + 1
Next
Next
j = 0
Z = K + 1
K = K + 1
Worksheets("Complexity").Range("B" & Z & ":OU" & Z) = complexity_count_array() 'Paste in the complexity count for each engineer/each week in the chart data page
Next
Worksheets("Raw Data").ShowAllData
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran in " & SecondsElapsed & " Seconds", vbInformation
Application.StatusBar = "Process 8 of 8 Complete"
Application.StatusBar = ""
End SubI'm sure there is a better way to go about what I am doing. I just don't know what that looks like! I know there is a lot of overhead with looping through data but I don't know of a way around it in the situation that I have.
