Selected Answer
Hi Kmowersvba and welcome to the Forum.
REVISION 2: 04 Jan 2023 (based on attached file and extra detail in discussion).
As a starter for your code, it's often easier to use Cells (rather than Range) since it has two arguments (rows and columns) so your loop counter can be used to loop in either direction.
In attached file, the revised code below is in a standard module. There's a button on the Summary sheet labelled "Update training status" to which the macro is assigned- click it and it will run. It uses nested loop and has comments to explain what's happening.
Additionally it now doesn't need SOURCE to have equal columns per AgentID row and iy vollects and entries in SOURCE for which there are no matching headers in the Summary sheet (in SOURCE the cells in yellow have changed values with D4 as a space -not a blank, unlike E2 which is one). These get reported in a message box at the end:
Sub NestedLoop_GenesysCloudReport()
Dim SumWs As Worksheet, Ws As Worksheet
Dim SumLstRw As Long, LstRw As Long, SumLstCol As Long, LstCol As Long
Dim c As Long, m As Long, n As Long, p As Long, StaCol As Long
Dim MissSta As String
Set Ws = ThisWorkbook.Worksheets("Source")
Set SumWs = ThisWorkbook.Worksheets("Summary")
On Error Resume Next
'determine last rows
LstRw = Ws.Cells(Rows.Count, 2).End(xlUp).Row
SumLstRw = SumWs.Cells(Rows.Count, 1).End(xlUp).Row
'determine last columns
LstCol = Ws.Cells(1, 1).CurrentRegion.Columns.Count ' since there aren't header rows after B
SumLstCol = SumWs.Cells(1, Columns.Count).End(xlToLeft).Column
' overwrite any previous report
SumWs.Range("B2:G" & SumLstRw).Value = ""
' ### find latest report dates
'loop down known agents in Summary
For c = 2 To SumLstRw
'loop down AgentsID in source
For m = 2 To LstRw
' compare value in summary column A with Source column B
If SumWs.Cells(c, 1).Value = Ws.Cells(m, 2).Value Then
'if matches, loop across Source
For n = 3 To LstCol
' reset state column
StaCol = 0
'get matching column number in summary
StaCol = WorksheetFunction.Match(Ws.Cells(m, n).Value, SumWs.Rows(1), 0)
If StaCol >= 1 Then
If Ws.Cells(m, 1).Value > SumWs.Cells(c, StaCol).Value Then
'overwrite date if more recent
SumWs.Cells(c, StaCol).Value = Ws.Cells(m, 1).Value
End If
Else
' if blank cell or state header not found, add to missing states
If Ws.Cells(m, n).Value <> "" Then MissSta = MissSta & Ws.Cells(m, n).Address(0, 0) & ":" & Ws.Cells(m, n).Value & "; "
End If
Next n
End If
Next m
' convert dates to status
For p = 2 To SumLstCol
Select Case SumWs.Cells(c, p).Value
Case ""
SumWs.Cells(c, p).Value = "No"
Case Is >= Date - 30
SumWs.Cells(c, p).Value = "Yes"
Case Is < Date - 30
SumWs.Cells(c, p).Value = "Yes, needs uptrain"
End Select
Next p
Next c
' tell user is any states were in source but not Summary
If MissSta > "" Then
MsgBox "Updated but didn't find headers for these entries in " & Ws.Name & vbCr & vbCr & MissSta
Else
MsgBox "Updated without problems " & Now
End If
End Sub
It will work with more agents and states, provided they're in column A and row 1 of the Summary sheet. For test purposes, you might want to comment out the Select Case (like a fancy If, EsleIf ,Else) section in bold above- you'll then be able to check that it picked up the right dates.
Hope this fixes your problem- if so, please remember to mark this answer as Selected.