Duplicating and amending rows in VBA based on text in a column

[K0D54]

Hey everyone! Does anyone know how I would search a column for a particular value and duplicate the row in which the value is found in VBA? I also want to amend a cell in the duplicated row if possible. I would like to take the top table and make it look like the bottom table in the included example. I want to duplicate any row with salary and add “- Other” to the employee’s name.



I tried doing something like this:

For i = 11 To lastrow
ws.Select
If UCase(Cells(i, "C")) = "SALARY" And UCase(Cells(i, "A")) <> "" Then
Range(("A" & i) & ":" & ("R" & i)).Select
Range(("A" & i) & ":" & ("R" & i)).Copy
Selection.Insert Shift:=xlDown

However, it created a never-ending loop, and I am unsure how to amend the cell names for a duplicate row. Any help would be appreciated. Thank you!

 

[Colo]

Hi,
The following code is written based on the data on your screenshot.

Sub Sample()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If UCase(Cells(i, "B").Value) = "SALARY" And UCase(Cells(i, "A").Value) <> "" Then
            Rows(i).Copy
            Rows(i + 1).Insert Shift:=xlDown
            Cells(i + 1, "A").Value = Cells(i, "A").Value & " - Other"
        End If
    Next
    Application.CutCopyMode = False
End Sub
End Sub

 

[K0D54]

Hi,
The following code is written based on the data on your screenshot.

Sub Sample()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If UCase(Cells(i, "B").Value) = "SALARY" And UCase(Cells(i, "A").Value) <> "" Then
            Rows(i).Copy
            Rows(i + 1).Insert Shift:=xlDown
            Cells(i + 1, "A").Value = Cells(i, "A").Value & " - Other"
        End If
    Next
    Application.CutCopyMode = False
End Sub
End Sub

This worked perfectly. Thank you!