UPDATE: I was able to brute force what I'm trying to do and break my code into a few sections for each date on ResourceNeeds tab(second picture). This works, but its clunky. Still looking for a way to use a loop to loop through the dates on ResourceNeeds tab rather than having multiple sections of code for each date I'm searching for.
I've got a piece of code that loops through a range of cells, searching for a specific date, adding hours by a job type for how many times the date appears. For example, the macro searches I12:M15 looking for 12/1/2020 (text format 44166), if it see's this it then loops through the job type and adds the hours associated with that step. Once the macro finishes it placed the sum for that date and those job types on Sheet1. I would like to expand the date search and add another loop to have it search 7 day's based on the dates I input in Sheet1 B1:H1. I've tried a few things but can't get it to reference to Sheet1 and then go back to the Sheet with all the data and loop.
The first picture is the sheet I am looping through The second picture is the sheet I want to pull dates from and paste the summary once complete.
Current code: The current code is only set up for 1 specific date and pasting that on Sheet1.
Public Function SubAssyArray() As Variant
'Set up the array of sheets (tabs) that we will loop through in the routines below.
'To increase speed, limit the number of tabs in the array to only those you are using as in the example below:
SubAssyArray = Array("Widget1", "Widget2")
Dim y As Variant
End Function
Sub Resource_Overview() 'Summary of daily tasks by worktype
'Don't update the screens as the Macro runs.
Application.ScreenUpdating = False
Application.StatusBar = "Macro is running..."
'Turn off autocalculation to speed things up
Application.Calculation = xlCalculationManual
'Declare the variables we'll need
Dim ws As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim LastColumnP As Long 'this is for pulling resouce totals, used to count number of dates to SUM work type
Dim StartCell As Range
Dim i, j As Double 'for counters, using double to add up decimals
Dim Assy, Solder, QC, PPC As Double 'variables to hold std hours total
Dim rn As Worksheet 'declaring ResourceNeeds sheet as variable
Set rn = Worksheets("ResourceNeeds")
'Need to change the date format to comma style for the loop to search for the dates correclty, we will change the format back to date once loop completes
For Each y In SubAssyArray
Sheets(y).Activate
Set ws = ActiveSheet
Set StartCell = Range("I12")
With ws
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info
ws.Range(StartCell, ws.Cells(LastRow, LastColumn)).Style = "Comma"
End With
Next y
Set y = Nothing
'Make sure counters are set to 0
Assy = 0#
Solder = 0#
QC = 0#
PPC = 0#
'Find Last Row and Column
For Each y In SubAssyArray
Sheets(y).Activate
Set ws = ActiveSheet
Set StartCell = Range("I12")
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info
For i = 9 To LastColumn 'Set from which column number you want the loop to start from
For j = 12 To LastRow
If Cells(j, i).Value = rn.Cells(1, 4) Then
If Cells(1, i).Value = "Assy" Then
Assy = Assy + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "Solder" Then
Solder = Solder + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "QC" Then
QC = QC + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "PPC" Then
PPC = PPC + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
End If
End If
Next j
Next i
Next y
'Paste results from loops
rn.Cells(2, 2) = PPC
rn.Cells(3, 2) = Assy
rn.Cells(4, 2) = Solder
rn.Cells(5, 2) = QC
'Clear counters for next loop
Assy = 0#
Solder = 0#
QC = 0#
PPC = 0#
'Find Last Row and Column
For Each y In SubAssyArray
Sheets(y).Activate
Set ws = ActiveSheet
Set StartCell = Range("I12")
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info
For i = 9 To LastColumn 'Set from which column number you want the loop to start from
For j = 12 To LastRow
If Cells(j, i).Value = rn.Cells(1, 4) Then
If Cells(1, i).Value = "Assy" Then
Assy = Assy + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "Solder" Then
Solder = Solder + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "QC" Then
QC = QC + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "PPC" Then
PPC = PPC + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
End If
End If
Next j
Next i
Next y
'Paste results from loops
rn.Cells(2, 3) = PPC
rn.Cells(3, 3) = Assy
rn.Cells(4, 3) = Solder
rn.Cells(5, 3) = QC
'Clear counters for next loop
Assy = 0#
Solder = 0#
QC = 0#
PPC = 0#
'Find Last Row and Column
For Each y In SubAssyArray
Sheets(y).Activate
Set ws = ActiveSheet
Set StartCell = Range("I12")
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info
For i = 9 To LastColumn 'Set from which column number you want the loop to start from
For j = 12 To LastRow
If Cells(j, i).Value = rn.Cells(1, 4) Then
If Cells(1, i).Value = "Assy" Then
Assy = Assy + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "Solder" Then
Solder = Solder + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "QC" Then
QC = QC + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
ElseIf Cells(1, i).Value = "PPC" Then
PPC = PPC + Cells(7, i).Value 'looking at 7th row down to add up the STD HRs
End If
End If
Next j
Next i
Next y
'Paste results from loops
rn.Cells(2, 4) = PPC
rn.Cells(3, 4) = Assy
rn.Cells(4, 4) = Solder
rn.Cells(5, 4) = QC
'Clear counters for next loop
Assy = 0#
Solder = 0#
QC = 0#
PPC = 0#
'Need to change the date format to date format
For Each y In SubAssyArray
Sheets(y).Activate
Set ws = ActiveSheet
Set StartCell = Range("I12")
With ws
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = ws.Cells(StartCell.Row, ws.Columns.Count).End(xlToLeft).Column - 3 '-3 columns to not count need date or ECD info
ws.Range(StartCell, ws.Cells(LastRow, LastColumn)).NumberFormat = "mm/dd/yy;@"
End With
Next y
Set y = Nothing
'Progress
rn.Select
Application.StatusBar = "Macro is complete..."
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
MsgBox "The Macro has finished running."
End Sub
