Insert Comment in Cells Automatically When Countif Returns a Value

Hello all, it's my first time to post, so thanks in advance for your help and patience as this is a pretty hard request to describe.

I'm am building an Excel workbook that tracks changes to a retail chain's headcount. Headcout goes up and down via new hires and terminations (events) that happen in different locations, different dates, and with different rep types. I receive a transaction log weekly in Excel from our database that outlines each event, which includes the person's name, their ID number, rep type, and date it occurred (The Events Log tab in the attached workbook)

Each retail store has it's own tab in the same workbook (Friendswood, Houston, etc). Each store's tab shows the beginning headcount for each week, terminations, new hires, and an ending headcount for each week with those events figured in...all for each rep type. Terminations get negative numbers as they take away headcount, new hires get positives. I get the values for both of these events to populate in the site tabs by doing a bunch of countifs that pull from the Events Log.

My question here is twofold:
First, I'm stumped and need to have some help writing a process in VBA that would go to each store's tab, look and see if there are any values for Terminations and New Hires for all rep types, and if there is a value there other than zero, it needs to go back to the Events log and pull the name and effective date for the rep that was counted in the countif that populated the cell (ex. at Friendswood (cell B7), there was 1 termination the week of 6/30-7/6 for FT Regular Sales Specialist, I need to have a comment inserted in that cell that identifies the person's name and the effective date of the termination that caused that -1. If that number was -2 or -3, I would need each name and date listed in the comment as well. If the event has a value of zero, no comment needs to be inserted. I need it to do this for every week, every rep type, and for all events (terminations and new hires) I basically want to see who is terminating or brought on as a new hire each week with these comments at each location. Comments have to be used in this sheet to conform to our company's reporting structure (but if you have other suggestions to display this data, I'll certainly listen to it)

Second, I used some pretty primitive Countifs to populate the events in each location's tab, if there is a better/easier way to do it, I'd love your advice.

In reality, in my actual file I am using...I have 151 different location tabs, 5 different events that can happen, 4 different rep types, and 52 different weeks that it all can happen in. I really need something that can be easily replicated to each location's tab, and would have the ability to update itself when new events are added on a weekly basis.

I hope I have been clear on what I need, if not please let me know and I will clarify. Thanks all!!

I have two questions before I start chipping away at this:

1) Are entries going to be deleted from the Event Log, or once there, they'll always be there?

2) Do you want it to extract names from only the most current week or to redo every single week each time the code is run (in case of a backdated entry)?

And other than the countif formulas, I can't think of a better way to do that either, unless you incorporate that into the code.

Thanks Caligula for sifting thru all this mess! Here are your answers:

1. I plan on keeping the data in the log for ~13 weeks, then I'll just go into each site's tab and copy, paste special, values and comments to hardcode them in so the log can be shortened. If this causes serious issues then I can just keep the log as is.

2. I'd like for it to run all weeks to insert any backdating that may happen.

Thanks again!

Ok, so I believe I've got it. It puts a comment in each of the cells, provided that week started less than 100 days ago (2 days over 14 weeks). This should work on any number of worksheets, with any number of positions, provided it's in the same format as this is. I add to fix a few spelling errors to get it matching correctly (line 38 I believe). I used the current sheet's F2 cell as a temporary holding cell; you can replace it with a different cell or a DataObject if you want (I just don't like using DataObjects unless I have to).

I pasted the code in a CommandButton on the main page and tested it; it seems to work. As you can see, there's a variable entry for each "event," a line where the remaining events in that column is checked, a routine to find people that went through that event, and a select case statement for that event. That should help you work through the rest of your programming...

Let me know if you need anything else,
Chris

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim vTermsLeft As Byte, vRow As Integer, vRow2 As Integer, vCol As Byte, vSheet As Integer, _
vPos As String, vEmpSt As String, vDate1 As Date, vDate2 As Date, vHiresLeft As Byte
vSheet = 2
Do Until vSheet > Sheets.Count

    vCol = 2
ColLoop:
    Do Until vCol > Application.WorksheetFunction.CountA(Sheets(vSheet).Rows("3"))

    vDate1 = Left(Sheets(vSheet).Cells(3, vCol), InStr(Sheets(vSheet).Cells(3, vCol), "-") - 1)
        If vDate1 + 100 < Date Then
        vCol = vCol + 1
        GoTo ColLoop:
        End If
    vDate2 = Right(Sheets(vSheet).Cells(3, vCol), InStr(Sheets(vSheet).Cells(3, vCol), "-") - 1)
    
    vTermsLeft = Application.WorksheetFunction.CountIf(Sheets(vSheet).Range("a:a"), "Terminations")
    vHiresLeft = Application.WorksheetFunction.CountIf(Sheets(vSheet).Range("a:a"), "New Hires")
        
    vRow = 1
    
        Do Until vTermsLeft = 0 And vHiresLeft = 0
            Select Case Sheets(vSheet).Range("a" & vRow)
            
            Case "Terminations"
            vPos = Sheets(vSheet).Range("a" & vRow - 2)
            vPos = Right(vPos, Len(vPos) - InStr(vPos, " "))
            vEmpSt = Left(vPos, InStr(vPos, " ") - 1)
            vPos = Right(vPos, Len(vPos) - InStr(vPos, " "))
            Call FindTerms(vRow, vCol, vEmpSt, vPos, vDate1, vDate2, vSheet)
            vTermsLeft = vTermsLeft - 1
            
            Case "New Hires"
            vPos = Sheets(vSheet).Range("a" & vRow - 3)
            vPos = Right(vPos, Len(vPos) - InStr(vPos, " "))
            vEmpSt = Left(vPos, InStr(vPos, " ") - 1)
            vPos = Right(vPos, Len(vPos) - InStr(vPos, " "))
            Call FindHires(vRow, vCol, vEmpSt, vPos, vDate1, vDate2, vSheet)
            vHiresLeft = vHiresLeft - 1
        
            End Select
        vRow = vRow + 1
        Loop
    vCol = vCol + 1
    Loop
vSheet = vSheet + 1
Loop
End Sub
Sub FindTerms(ByVal vRow As Integer, vCol As Byte, vEmpSt As String, vPos As String, vDate1 As Date, vDate2 As Date, vSheet As Integer)
Dim vRow2 As Integer
    If Sheets(vSheet).Cells(vRow, vCol) <> 0 Then
        vRow2 = 2
        Sheets(vSheet).Cells(vRow, vCol).ClearComments
        Do Until vRow2 > Application.WorksheetFunction.CountA(Worksheets(1).Range("b:b"))
            If Worksheets(1).Range("f" & vRow2) = "Termination" _
            And Worksheets(1).Range("e" & vRow2) = Sheets(vSheet).Name _
            And Worksheets(1).Range("g" & vRow2) = Left(Sheets(vSheet).Range("a" & vRow - 2), 2) _
            And Worksheets(1).Range("c" & vRow2) = vPos _
            And Worksheets(1).Range("d" & vRow2) = vEmpSt _
            And Worksheets(1).Range("h" & vRow2) >= vDate1 _
            And Worksheets(1).Range("h" & vRow2) <= vDate2 Then
                If Sheets(vSheet).Range("f2") = vbNullString Then
                Sheets(vSheet).Range("f2") = Worksheets(1).Range("b" & vRow2)
                Else: Sheets(vSheet).Range("f2") = Sheets(vSheet).Range("f2") & vbLf & Worksheets(1).Range("b" & vRow2)
                End If
            End If
        vRow2 = vRow2 + 1
        Loop
        Sheets(vSheet).Cells(vRow, vCol).AddComment Sheets(vSheet).Range("f2").Value
        Sheets(vSheet).Range("f2") = vbNullString
    End If
End Sub

Sub FindHires(ByVal vRow As Integer, vCol As Byte, vEmpSt As String, vPos As String, vDate1 As Date, vDate2 As Date, vSheet As Integer)
Dim vRow2 As Integer
    If Sheets(vSheet).Cells(vRow, vCol) <> 0 Then
        vRow2 = 2
        Sheets(vSheet).Cells(vRow, vCol).ClearComments
        Do Until vRow2 > Application.WorksheetFunction.CountA(Worksheets(1).Range("b:b"))
            If Worksheets(1).Range("f" & vRow2) = "New Hire" _
            And Worksheets(1).Range("e" & vRow2) = Sheets(vSheet).Name _
            And Worksheets(1).Range("g" & vRow2) = Left(Sheets(vSheet).Range("a" & vRow - 3), 2) _
            And Worksheets(1).Range("c" & vRow2) = vPos _
            And Worksheets(1).Range("d" & vRow2) = vEmpSt _
            And Worksheets(1).Range("h" & vRow2) >= vDate1 _
            And Worksheets(1).Range("h" & vRow2) <= vDate2 Then
                If Sheets(vSheet).Range("f2") = vbNullString Then
                Sheets(vSheet).Range("f2") = Worksheets(1).Range("b" & vRow2)
                Else: Sheets(vSheet).Range("f2") = Sheets(vSheet).Range("f2") & vbLf & Worksheets(1).Range("b" & vRow2)
                End If
            End If
        vRow2 = vRow2 + 1
        Loop
        Sheets(vSheet).Cells(vRow, vCol).AddComment Sheets(vSheet).Range("f2").Value
        Sheets(vSheet).Range("f2") = vbNullString
    End If
End Sub

All hail Caligula! I'll see if I can insert it into my real working workbook and get it going. Thanks again....I'll let you know if I get stumped.