M
MikiV
Somebody please help. I have several excel spreadsheets which should be
combined and calculated in one document. I made macro to perform this
function but it is extremly slow (takes hours). I have tried everything,
maybe it just cannot go any faster because of the thousands of records that
it has to search and calculate.
Following is a small section of my code (the slowest sub), I have several
similar to this one. What it should do is go through all the records in tab
"RecordTable", search for all matching job codes and locations to specified
fields in the tab "Main Table", calculate the total count and input in
specified field in the "Main Table".
The code is correct, it's just way to slow. I have specified the "slow part"
below. Thanks in advance for any advice you can give me.
Private Sub CommandButton3_Click()
On Error Resume Next
Dim columncount As Integer
Dim columnnumber As Integer
Dim PCBOErowcount As Integer
Dim countMAINrow As Integer
Dim countAuthorized As Integer
Dim updateAuthorized As String
Dim matchjob As Integer
Dim findcolumns As Integer
Dim tablejobvalue As String
Dim jobvalue As String
Dim tablesitecount As Integer
Dim sitevalue As String
Dim headcount As String
Dim sitename As String
Dim numberofrecords As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Worksheets("RecordTable").Activate
Worksheets("RecordTable").Cells(2, 1).Activate
RecordTablerowcount = ActiveCell.CurrentRegion.Rows.Count
Worksheets("Main Table").Activate
Worksheets("Main Table").Cells(3, 1).Activate
countMAINrow = ActiveCell.CurrentRegion.Rows.Count - 1
Worksheets("Main Table").Cells(3, 3).Activate
columncount = ActiveCell.CurrentRegion.Columns.Count - 4
tablesitecount = 505
Worksheets("Main Table").Activate
Worksheets("Main Table").Cells(3, 3).Activate
For columnnumber = 3 To columncount
sitename = Worksheets("Main Table").Cells(tablesitecount, 2).Value
For countAuthorized = 3 To countMAINrow
tablejobvalue = Worksheets("Main Table").Cells(countAuthorized, 1).Value
numberofrecords = 0
''''''' Slow Part
For matchjob = 2 To RecordTablerowcount
jobvalue = Worksheets("RecordTable").Cells(matchjob, 10).Value
sitevalue = Worksheets("RecordTable").Cells(matchjob, 3).Value
headcount = Worksheets("RecordTable").Cells(matchjob, 13).Value
If jobvalue = tablejobvalue And sitevalue = sitename And headcount =
"1" Then
numberofrecords = numberofrecords + 1
End If
''''''' End of Slow Part
Next matchjob
Worksheets("Main Table").Cells(countAuthorized, columnnumber).Value =
numberofrecords
Next countAuthorized
tablesitecount = tablesitecount + 1
columnnumber = columnnumber + 3
Next columnnumber
ActiveWorkbook.Save
End Sub
combined and calculated in one document. I made macro to perform this
function but it is extremly slow (takes hours). I have tried everything,
maybe it just cannot go any faster because of the thousands of records that
it has to search and calculate.
Following is a small section of my code (the slowest sub), I have several
similar to this one. What it should do is go through all the records in tab
"RecordTable", search for all matching job codes and locations to specified
fields in the tab "Main Table", calculate the total count and input in
specified field in the "Main Table".
The code is correct, it's just way to slow. I have specified the "slow part"
below. Thanks in advance for any advice you can give me.
Private Sub CommandButton3_Click()
On Error Resume Next
Dim columncount As Integer
Dim columnnumber As Integer
Dim PCBOErowcount As Integer
Dim countMAINrow As Integer
Dim countAuthorized As Integer
Dim updateAuthorized As String
Dim matchjob As Integer
Dim findcolumns As Integer
Dim tablejobvalue As String
Dim jobvalue As String
Dim tablesitecount As Integer
Dim sitevalue As String
Dim headcount As String
Dim sitename As String
Dim numberofrecords As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Worksheets("RecordTable").Activate
Worksheets("RecordTable").Cells(2, 1).Activate
RecordTablerowcount = ActiveCell.CurrentRegion.Rows.Count
Worksheets("Main Table").Activate
Worksheets("Main Table").Cells(3, 1).Activate
countMAINrow = ActiveCell.CurrentRegion.Rows.Count - 1
Worksheets("Main Table").Cells(3, 3).Activate
columncount = ActiveCell.CurrentRegion.Columns.Count - 4
tablesitecount = 505
Worksheets("Main Table").Activate
Worksheets("Main Table").Cells(3, 3).Activate
For columnnumber = 3 To columncount
sitename = Worksheets("Main Table").Cells(tablesitecount, 2).Value
For countAuthorized = 3 To countMAINrow
tablejobvalue = Worksheets("Main Table").Cells(countAuthorized, 1).Value
numberofrecords = 0
''''''' Slow Part
For matchjob = 2 To RecordTablerowcount
jobvalue = Worksheets("RecordTable").Cells(matchjob, 10).Value
sitevalue = Worksheets("RecordTable").Cells(matchjob, 3).Value
headcount = Worksheets("RecordTable").Cells(matchjob, 13).Value
If jobvalue = tablejobvalue And sitevalue = sitename And headcount =
"1" Then
numberofrecords = numberofrecords + 1
End If
''''''' End of Slow Part
Next matchjob
Worksheets("Main Table").Cells(countAuthorized, columnnumber).Value =
numberofrecords
Next countAuthorized
tablesitecount = tablesitecount + 1
columnnumber = columnnumber + 3
Next columnnumber
ActiveWorkbook.Save
End Sub