Finding a specific name in a row and then adding row below

[ericpny2]

I am trying to cycle through rows in column 4 to find a manually inserted person in that column. Once the name is found, i would like to add a row below that, and then i would like the function to stop there. Names will be listed more than once and i dont want it to add a row every time it finds the name, just the first time. the names start on row 5

I also want it to alert me with a message box if the inputed name is not listed

anything in red is what i know is wrong

please help me out

Sub addequipment()
Dim ws As Worksheet
Dim foremancol As String

Dim foreman As String, i As Long, count As Long

Set ws = ActiveSheet
foreman = Application.InputBox("Which Foreman is adding a Piece of Equipment")

For i = 0 To end
foremancol = ws.Cells(5 + i, 4)

If foremancol = foreman Then
'insert a row below
ActiveCell.Offset(1).EntireRow.Insert
End If
Next i

End Sub

 

[GTO]

Try:

Option Explicit
        
Sub addequipment()
Dim ws As Worksheet
Dim foreman As String
    
Dim rngForemenNames As Range
Dim lRow As Long
    
    Set ws = ActiveSheet
    foreman = Application.InputBox("Which Foreman is adding a Piece of Equipment")
    
    '// Set a reference to the range D5:D(last row on sheet)    //
    Set rngForemenNames = Range(ws.Cells(5, 4), ws.Cells(ws.Rows.count, 4).End(xlUp))
    
    '// Allow an error to be passed by, just for the next line. If we don't find    //
    '// the name entered, lRow will remain zero.                                    //
    On Error Resume Next
    lRow = Application.Match(foreman, rngForemenNames, 0)
    On Error GoTo 0
    '//Zero will evaluate to False                                                  //
    If Not CBool(lRow) Then
        MsgBox foreman & " was not found", vbInformation, vbNullString
        Exit Sub
    Else
        ws.Rows(lRow + 5).Insert
    End If
End Sub

Hope that helps,

Mark