Advanced help needed: Refinement of clumsy code

carcharoth2554

New Member
Joined
Feb 5, 2013
Messages
30
Hi guys,
Excel 2010 on Win 7 64bit
Below is my code to date that in short outlines the behaviours of my reporting function. This code is run from a master workbook that references tables of data contained within several other open workbooks that are hidden in the background. Workbooks represent Projects (currently 11 workbooks), worksheets represent Sub-Projects (currently 68 worksheets spread across the 11 workbooks) and each worksheet contains one table of data averaging around 20 x 146 cells in size. All table data is number format with 1 decimal place but there are many empty cells.
The reporting function finds where data is present on the table by looping through all cells, runs through some IF statements to define the correct behaviours and then sends the totals back to the master report sheet.
The code actually functioned perfectly, doing exactly what I wanted it to until I loaded a simple 13 x 11 array of wages (which I did to try to refine the code) which it then used in some cases to calculate an hourly cost. Once I did this, the processing time for the reporting function jumped from around 12 seconds to around 42 seconds. It still does exactly what I want; I just get worried about crashing especially if I added more data.
As you can see from the code yourself, there is a great deal of unnecessary & clumsy code remaining so I would love some help so that first I can cut some of this down, and second, bring that processing time as far down as possible.
As I said, I think this is an advanced one so would be grateful for a veteran to take a gander.
Thanks guys, I know you will come through for me!
Code:
Function dynamicArray(lookupValue As String, lookupDate As Long, table As Range)
 
  ' Define the row starting cell, Row n maps to the first row where the range is found in the spreadsheet.
  ' Row 1 is the first row and 1,048,576 is the last possible row.
  Dim rowStart As Integer
  Dim rowSize As Integer
 
  ' Define the column starting cell, Column A maps to 1, Column B maps to 2, et cetera.
  ' Column A is the first column and XFD is the last possible column.
  Dim columnStart As Integer
  Dim columnSize As Integer
 
  ' Create a dynamic multiple dimension array without any physical size.
  Dim multidimensionArray As Variant
 
  ' Define and declare a local returnValue variable.
  Dim returnValue As Double
  'returnValue = False
 
  ' Assign the starting row and column values, and the length of values.
  ' Since you need to add the row and column to the starting grid coordinates, you need to use 0-based numbering,
  ' which means you subtract one from the length.
  rowStart = table.Row
  rowSize = table.Rows.Count - 1
  columnStart = table.Column
  columnSize = table.Columns.Count - 1
 
  ' This demonstrates that the range starts in the row and column, and
  ' the length and width of the multiple dimension array.
  ' ----------------------------------------------------------------------
  ' Insert single quotes for the next two lines to suppress testing the program with variables.
  'MsgBox ("(RowStart [" + CStr(rowStart) + "], (ColStart [" + CStr(columnStart) + "]) " + _
    '"(RowSize [" + CStr(rowSize) + "] ColSize [" + CStr(columnSize) + "])")
  ' ----------------------------------------------------------------------
 
  ' Redimension the arrays maximum size, rows first, columns second.
  ReDim multidimensionArray(rowSize, columnSize)
 
  ' Read through the range and assign it to a local and dynamically sized array variable.
  ' An important note to those unfamilar with the Cells function, it works on the active worksheet and uses two
  ' parameters, the absolute row and column number or relative row and column numbers. In this sample, the
  ' easiest solution is to use absolute row an dolumn numbers.
  For i = 0 To rowSize
    For j = 0 To columnSize
      multidimensionArray(i, j) = CStr(Workbooks("PD0M - Master Tracker").Worksheets("Resources").Cells(rowStart + i, columnStart + j))
    Next j
  Next i
 
  ' Read through the local variable range and view the content set.
  For i = 0 To rowSize
    For j = 0 To columnSize
      ' Check if the lookupValue has been found and return true.
      If lookupValue = multidimensionArray(i, j) Then
            If lookupDate = "2012" Then
                returnValue = multidimensionArray(i, j + 4)
            ElseIf lookupDate = "2013" Then
                returnValue = multidimensionArray(i, j + 6)
            ElseIf lookupDate = "2014" Then
                returnValue = multidimensionArray(i, j + 8)
            ElseIf lookupDate = "2015" Then
                returnValue = multidimensionArray(i, j + 10)
            End If
        Exit For
      End If
    Next j
  Next i
 
  ' Return a Boolean value: true when found and false when not found.
  dynamicArray = returnValue
End Function
Private Sub CommandButton1_Click()
    'Define variables
    Dim sdsheet As Worksheet, ersheet As Worksheet
    'Local Variables
    Dim trackerAddress As String
    Dim wks As Worksheet
    Dim strSubAddress As String
    Dim strDisplayText As String
    Dim resourceSheet As Worksheet
    Dim resourceDest As Worksheet
    Dim delDescrip As String
    Dim profitTotal As Double
    Dim reportYear As Long
    Dim wageYear As Long
    Dim week As Integer
    Dim taskCode, month As String
    Dim name As String
    Dim wageReturn As Double
    Dim u As Long, c As Long
    Dim a
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set sdsheet = ThisWorkbook.Sheets("Summary")
    Set ersheet = ThisWorkbook.Sheets("Report Totals")
    ersheet.Range("A1:J33").ClearContents
    ersheet.Range("A1:J1") = Array("Project", "Total Budget (Hours)", "Total Worked (Hours)", "Total Remainder (Hours)", "Percentage of Budgeted Hours Used (%)", "Total Budget (£)", "Total Spend (£)", "Total Remainder (£)", "Percentage of Budget Used (%)", "Percentage of Deliverable Complete (%)")
    If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
        sdLR = 2
    Else
        sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
        End If
        
    Y = 2 'starting row
        
    For X = 2 To sdLR
            'put on the ersheet
            ersheet.Cells(Y, 1) = sdsheet.Cells(X, 2)
            ersheet.Cells(Y, 2) = sdsheet.Cells(X, 3)
            ersheet.Cells(Y, 3) = sdsheet.Cells(X, 4)
            ersheet.Cells(Y, 4) = sdsheet.Cells(X, 5)
            ersheet.Cells(Y, 5) = sdsheet.Cells(X, 6)
            ersheet.Cells(Y, 6) = sdsheet.Cells(X, 7)
            ersheet.Cells(Y, 7) = sdsheet.Cells(X, 8)
            ersheet.Cells(Y, 8) = sdsheet.Cells(X, 9)
            ersheet.Cells(Y, 9) = sdsheet.Cells(X, 10)
            ersheet.Cells(Y, 10) = sdsheet.Cells(X, 11)
            Y = Y + 1
    Next X
    ersheet.Range("A2") = "Totals:"
    'Define static parameters
    reportYear = Workbooks("PD0M - Master Tracker").Worksheets("Report Totals").Range("F66").Value
    month = Workbooks("PD0M - Master Tracker").Worksheets("Report Totals").Range("E66").Value
    week = Workbooks("PD0M - Master Tracker").Worksheets("Report Totals").Range("D66").Value - 2
    For i = 3 To 99
        If Workbooks("PD0M - Master Tracker").Worksheets("Summary").Cells(i, 1) <> "" Then
            'Define workbook needed and activate
            trackerAddress = Workbooks("PD0M - Master Tracker").Worksheets("Summary").Cells(i, 1).Value
            With Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets("Summary")
                .Range("D68:K96").ClearContents
                .Range("D68:K96").Interior.ColorIndex = 24
                
                'Resource cost calculating method
                For Each wks In Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets
                    strDisplayText = wks.name
                    Set resourceDest = Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText)
                    If strDisplayText <> "Summary" Then
                        If strDisplayText <> "Resources" Then
                            z = 9
                            Do While Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(6, z) <> ""
                                profitTotal = 0
                                X = 11
                                For X = 11 To 146
                                    taskCode = ""
                                    If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z) <> "" Then
                                        taskCode = Right(Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(7, z), 2)
                                        name = Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(6, z)
                                        wageYear = Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3)
                                        wageReturn = dynamicArray(name, wageYear, Workbooks("PD0M - Master Tracker").Worksheets("Resources").Range("A13:K50"))
                                        
                                        If taskCode = "NC" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                NC2 = NC2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                NC7 = NC7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                NC3 = NC3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                NC6 = NC6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                NC4 = NC4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                NC5 = NC5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            'totals
                                            NC1 = NC1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            NC8 = NC8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "TR" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                TR2 = TR2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TR7 = TR7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                TR3 = TR3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TR6 = TR6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                TR4 = TR4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TR5 = TR5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            TR1 = TR1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            TR8 = TR8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "MD" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                MD2 = MD2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MD7 = MD7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                MD3 = MD3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MD6 = MD6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                MD4 = MD4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MD5 = MD5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            MD1 = MD1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            MD8 = MD8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "FA" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                FA2 = FA2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                FA7 = FA7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                FA3 = FA3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                FA6 = FA6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                FA4 = FA4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                FA5 = FA5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            FA1 = FA1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            FA8 = FA8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "CP" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                CP2 = CP2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                CP7 = CP7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                CP3 = CP3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                CP6 = CP6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                CP4 = CP4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                CP5 = CP5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            CP1 = CP1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            CP8 = CP8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "RW" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                RW2 = RW2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                RW7 = RW7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                RW3 = RW3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                RW6 = RW6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                RW4 = RW4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                RW5 = RW5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            RW1 = RW1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            RW8 = RW8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "RE" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                RE2 = RE2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                RE7 = RE7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                RE3 = RE3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                RE6 = RE6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                RE4 = RE4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                RE5 = RE5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            RE1 = RE1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            RE8 = RE8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "SC" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                SC2 = SC2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                SC7 = SC7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                SC3 = SC3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                SC6 = SC6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                SC4 = SC4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                SC5 = SC5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            SC1 = SC1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            SC8 = SC8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "CA" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                CA2 = CA2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                CA7 = CA7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                CA3 = CA3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                CA6 = CA6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                CA4 = CA4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                CA5 = CA5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            CA1 = CA1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            CA8 = CA8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "PM" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                PM2 = PM2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                PM7 = PM7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                PM3 = PM3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                PM6 = PM6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                PM4 = PM4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                PM5 = PM5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            PM1 = PM1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            PM8 = PM8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "AL" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                AL2 = AL2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AL7 = AL7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                AL3 = AL3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AL6 = AL6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                AL4 = AL4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AL5 = AL5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            AL1 = AL1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            AL8 = AL8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "LL" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                LL2 = LL2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                LL7 = LL7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                LL3 = LL3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                LL6 = LL6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                LL4 = LL4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                LL5 = LL5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            LL1 = LL1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            LL8 = LL8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "SL" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                SL2 = SL2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                SL7 = SL7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                SL3 = SL3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                SL6 = SL6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                SL4 = SL4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                SL5 = SL5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            SL1 = SL1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            SL8 = SL8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "MP" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                MP2 = MP2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MP7 = MP7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                MP3 = MP3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MP6 = MP6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                MP4 = MP4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MP5 = MP5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            MP1 = MP1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            MP8 = MP8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "MG" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                MG2 = MG2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MG7 = MG7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                MG3 = MG3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MG6 = MG6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                MG4 = MG4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                MG5 = MG5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            MG1 = MG1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            MG8 = MG8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "TB" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                TB2 = TB2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TB7 = TB7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                TB3 = TB3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TB6 = TB6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                TB4 = TB4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TB5 = TB5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            TB1 = TB1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            TB8 = TB8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "ST" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                ST2 = ST2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                ST7 = ST7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                ST3 = ST3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                ST6 = ST6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                ST4 = ST4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                ST5 = ST5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            ST1 = ST1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            ST8 = ST8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "PT" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                PT2 = PT2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                PT7 = PT7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                PT3 = PT3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                PT6 = PT6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                PT4 = PT4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                PT5 = PT5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            PT1 = PT1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            PT8 = PT8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "TT" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                TT2 = TT2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TT7 = TT7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                TT3 = TT3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TT6 = TT6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                TT4 = TT4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                TT5 = TT5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            TT1 = TT1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            TT8 = TT8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "WT" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                WT2 = WT2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                WT7 = WT7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                WT3 = WT3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                WT6 = WT6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                WT4 = WT4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                WT5 = WT5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            WT1 = WT1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            WT8 = WT8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "AP" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                AP2 = AP2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AP7 = AP7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                AP3 = AP3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AP6 = AP6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                AP4 = AP4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AP5 = AP5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            AP1 = AP1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            AP8 = AP8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        ElseIf taskCode = "AG" Then
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear Then
                                                AG2 = AG2 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AG7 = AG7 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 1).Value = month Then
                                                AG3 = AG3 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AG6 = AG6 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            If Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 3).Value = reportYear And Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, 2).Value = week Then
                                                AG4 = AG4 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                                AG5 = AG5 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                            End If
                                            AG1 = AG1 + Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z).Value
                                            AG8 = AG8 + (wageReturn * Workbooks(trackerAddress & " - Tracker.xlsm").Worksheets(strDisplayText).Cells(X, z))
                                        End If
                                    End If
                                Next
                            z = z + 1
                            Loop
                        End If
                    End If
                  Next wks
                End With
        End If
    Next i
    With Workbooks("PD0M - Master Tracker").Worksheets("Report Totals")
    '########################################  Array
        .Cells(69, 4) = NC4
        .Cells(69, 6) = NC3
        .Cells(69, 8) = NC2
        .Cells(69, 10) = NC1
        .Cells(69, 11) = NC8
        .Cells(69, 9) = NC7
        .Cells(69, 7) = NC6
        .Cells(69, 5) = NC5
        
        .Cells(70, 4) = TR4
        .Cells(70, 6) = TR3
        .Cells(70, 8) = TR2
        .Cells(70, 10) = TR1
        .Cells(70, 11) = TR8
        .Cells(70, 9) = TR7
        .Cells(70, 7) = TR6
        .Cells(70, 5) = TR5
        
        .Cells(71, 4) = MD4
        .Cells(71, 6) = MD3
        .Cells(71, 8) = MD2
        .Cells(71, 10) = MD1
        .Cells(71, 11) = MD8
        .Cells(71, 9) = MD7
        .Cells(71, 7) = MD6
        .Cells(71, 5) = MD5
        
        .Cells(72, 4) = FA4
        .Cells(72, 6) = FA3
        .Cells(72, 8) = FA2
        .Cells(72, 10) = FA1
        .Cells(72, 11) = FA8
        .Cells(72, 9) = FA7
        .Cells(72, 7) = FA6
        .Cells(72, 5) = FA5
        
        .Cells(73, 4) = CP4
        .Cells(73, 6) = CP3
        .Cells(73, 8) = CP2
        .Cells(73, 10) = CP1
        .Cells(73, 11) = CP8
        .Cells(73, 9) = CP7
        .Cells(73, 7) = CP6
        .Cells(73, 5) = CP5
        
        .Cells(74, 4) = RW4
        .Cells(74, 6) = RW3
        .Cells(74, 8) = RW2
        .Cells(74, 10) = RW1
        .Cells(74, 11) = RW8
        .Cells(74, 9) = RW7
        .Cells(74, 7) = RW6
        .Cells(74, 5) = RW5
        
        .Cells(75, 4) = RE4
        .Cells(75, 6) = RE3
        .Cells(75, 8) = RE2
        .Cells(75, 10) = RE1
        .Cells(75, 11) = RE8
        .Cells(75, 9) = RE7
        .Cells(75, 7) = RE6
        .Cells(75, 5) = RE5
        
        .Cells(76, 4) = SC4
        .Cells(76, 6) = SC3
        .Cells(76, 8) = SC2
        .Cells(76, 10) = SC1
        .Cells(76, 11) = SC8
        .Cells(76, 9) = SC7
        .Cells(76, 7) = SC6
        .Cells(76, 5) = SC5
        
        .Cells(77, 4) = CA4
        .Cells(77, 6) = CA3
        .Cells(77, 8) = CA2
        .Cells(77, 10) = CA1
        .Cells(77, 11) = CA8
        .Cells(77, 9) = CA7
        .Cells(77, 7) = CA6
        .Cells(77, 5) = CA5
        
        .Cells(78, 4) = PM4
        .Cells(78, 6) = PM3
        .Cells(78, 8) = PM2
        .Cells(78, 10) = PM1
        .Cells(78, 11) = PM8
        .Cells(78, 9) = PM7
        .Cells(78, 7) = PM6
        .Cells(78, 5) = PM5
        
        .Cells(79, 4) = AL4
        .Cells(79, 6) = AL3
        .Cells(79, 8) = AL2
        .Cells(79, 10) = AL1
        .Cells(79, 11) = AL8
        .Cells(79, 9) = AL7
        .Cells(79, 7) = AL6
        .Cells(79, 5) = AL5
        
        .Cells(80, 4) = LL4
        .Cells(80, 6) = LL3
        .Cells(80, 8) = LL2
        .Cells(80, 10) = LL1
        .Cells(80, 11) = LL8
        .Cells(80, 9) = LL7
        .Cells(80, 7) = LL6
        .Cells(80, 5) = LL5
        
        .Cells(81, 4) = SL4
        .Cells(81, 6) = SL3
        .Cells(81, 8) = SL2
        .Cells(81, 10) = SL1
        .Cells(81, 11) = SL8
        .Cells(81, 9) = SL7
        .Cells(81, 7) = SL6
        .Cells(81, 5) = SL5
        
        .Cells(82, 4) = MP4
        .Cells(82, 6) = MP3
        .Cells(82, 8) = MP2
        .Cells(82, 10) = MP1
        .Cells(82, 11) = MP8
        .Cells(82, 9) = MP7
        .Cells(82, 7) = MP6
        .Cells(82, 5) = MP5
        
        .Cells(83, 4) = MG4
        .Cells(83, 6) = MG3
        .Cells(83, 8) = MG2
        .Cells(83, 10) = MG1
        .Cells(83, 11) = MG8
        .Cells(83, 9) = MG7
        .Cells(83, 7) = MG6
        .Cells(83, 5) = MG5
        
        .Cells(84, 4) = TB4
        .Cells(84, 6) = TB3
        .Cells(84, 8) = TB2
        .Cells(84, 10) = TB1
        .Cells(84, 11) = TB8
        .Cells(84, 9) = TB7
        .Cells(84, 7) = TB6
        .Cells(84, 5) = TB5
        
        .Cells(85, 4) = ST4
        .Cells(85, 6) = ST3
        .Cells(85, 8) = ST2
        .Cells(85, 10) = ST1
        .Cells(85, 11) = ST8
        .Cells(85, 9) = ST7
        .Cells(85, 7) = ST6
        .Cells(85, 5) = ST5
        
        .Cells(86, 4) = PT4
        .Cells(86, 6) = PT3
        .Cells(86, 8) = PT2
        .Cells(86, 10) = PT1
        .Cells(86, 11) = PT8
        .Cells(86, 9) = PT7
        .Cells(86, 7) = PT6
        .Cells(86, 5) = PT5
        
        .Cells(87, 4) = TT4
        .Cells(87, 6) = TT3
        .Cells(87, 8) = TT2
        .Cells(87, 10) = TT1
        .Cells(87, 11) = TT8
        .Cells(87, 9) = TT7
        .Cells(87, 7) = TT6
        .Cells(87, 5) = TT5
        
        .Cells(88, 4) = WT4
        .Cells(88, 6) = WT3
        .Cells(88, 8) = WT2
        .Cells(88, 10) = WT1
        .Cells(88, 11) = WT8
        .Cells(88, 9) = WT7
        .Cells(88, 7) = WT6
        .Cells(88, 5) = WT5
        
        .Cells(89, 4) = AP4
        .Cells(89, 6) = AP3
        .Cells(89, 8) = AP2
        .Cells(89, 10) = AP1
        .Cells(89, 11) = AP8
        .Cells(89, 9) = AP7
        .Cells(89, 7) = AP6
        .Cells(89, 5) = AP5
        
        .Cells(90, 4) = AG4
        .Cells(90, 6) = AG3
        .Cells(90, 8) = AG2
        .Cells(90, 10) = AG1
        .Cells(90, 11) = AG8
        .Cells(90, 9) = AG7
        .Cells(90, 7) = AG6
        .Cells(90, 5) = AG5
        
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top