VBA to search string and insert row if string not found

[DDT~123]

Greetings All,

I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.

I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.

Your help would be greatly appreciated.

 

[JLGWhiz]

I did some guessing to construct this, but hopefully it will work.

Sub MeetingBreak()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
        If .Cells(i, 1) <> .Cells(i - 1, 1) And LCase(.Cells(i - 1, 1).Value) <> "break" Then
            If LCase(.Cells(i, 1).Value) <> "break" And LCase(.Cells(i, 1).Value) <> "meeting" Then
                .Rows(i).Insert
                .Cells(i, 1) = "Break"
            End If
        End If
        If LCase(.Cells(i, 1)) = "break" And LCase(.Cells(i - 1, 1).Value) <> "meeting" Then
            .Rows(i).Insert
            .Cells(i, 1) = "Meeting"
        End If
        End With
    Next
End Sub

 

[DDT~123]

Thank you for replying, JLGWhiz. I'm needing to insert a row between two two agent's names if a condition is not met. Column A may look like this:

Employee: Doe, John
Schedule
Activity Type
Presence
Break
Meeting
Total
Employee: Sossa, Sammy
Schedule
Activity Type
Presence
Break
Total
Employee: Cole, Keisha
Schedule
Activity Type
Presence
Meeting
Total
Employee: Manaj, Niki
Schedule
Activity Type
Presence
Total

Using Sammy Sossa as an example... He did not have any meetings that day, therefore "Meeting" did not appear on the report. I'm needing to insert a row and add the string "Meeting" before continuing to the next agent named Keisha Cole. For Keisha, she did not take a break therefore needing to insert a row above Niki which has "Break" in column A. Nikki did not take any breaks nor have any meetings, she would need two rows inserted before moving on to the next agent.

 

[JLGWhiz]

It helps to cut down on wasted effort if the details are included in the original post. Fortunately, I think some minor changes will still make this work. Give it a try and post back if you have a problem with it.

Sub MeetingBreak2()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        With sh
        If LCase(.Cells(i, 1).Value) = "total" And LCase(.Cells(i - 1, 1).Value) <> "meeting" Then
            If LCase(.Cells(i, 1).Value) <> "meeting" And LCase(.Cells(i, 1).Value) <> "break" Then
                .Rows(i).Insert
                .Cells(i, 1) = "Meeting"
            End If
        End If
        If LCase(.Cells(i, 1)) = "meeting" And LCase(.Cells(i - 1, 1).Value) <> "break" Then
            .Rows(i).Insert
            .Cells(i, 1) = "Break"
        End If
        End With
    Next
End Sub

 

[Rick Rothstein]

Thank you for replying, JLGWhiz. I'm needing to insert a row between two two agent's names if a condition is not met. Column A may look like this:

Employee: Doe, John
Schedule
Activity Type
Presence
Break
Meeting
Total
Employee: Sossa, Sammy
Schedule
Activity Type
Presence
Break
Total
Employee: Cole, Keisha
Schedule
Activity Type
Presence
Meeting
Total
Employee: Manaj, Niki
Schedule
Activity Type
Presence
Total

Using Sammy Sossa as an example... He did not have any meetings that day, therefore "Meeting" did not appear on the report. I'm needing to insert a row and add the string "Meeting" before continuing to the next agent named Keisha Cole. For Keisha, she did not take a break therefore needing to insert a row above Niki which has "Break" in column A. Nikki did not take any breaks nor have any meetings, she would need two rows inserted before moving on to the next agent.

A clarification please... is the end result of your request to have these five lines of text (in Column A) between every listed employee...

Schedule
Activity Type
Presence
Break
Meeting
Total

so that if one or more of them are missing, you want to insert them? Also, are the names (Doe, John) in the same cell in Column A as the word "Employee:" or are they in Column B?

 

[DDT~123]

Yes... Each employee has a variable amount of rows containing detail of their Schedule and Activity Type, but the last 4 rows is the summary of the above detail which includes Presence, Break, Meeting, and Total. Employee: [agent name] is in a single cell in column A and not seperated into column B.

I have another macro which searches for the words "Presence, Break, Meeting, Total" and places an "X" in front of them, then deletes all the rows which do NOT have an "X" as well as the blank rows. Therefore, the only rows which are not deleted are Presence, Break, Meeting, Total. The end result is not a detailed report, but just a summary of those 4 items.