Try:
Sub Search_Command()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim icell As Range
Dim myValue As String
Application.ScreenUpdating = False
myValue = ws.Range("C2").Value
If myValue = "" Then
MsgBox ("You can't search without a search value.")
Exit Sub
End If
For Each icell In ws.Range("B12:K" & ws.Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
If Not IsEmpty(ws.Range("B9")) Then
MsgBox ("There is no more room in rows 5 thru 9 to enter search data. Please clear the cells.")
Exit Sub
End If
If Not InStr(1, icell.Value, myValue) = 0 Then
ws.Range("B" & icell.Row, "K" & icell.Row).Copy Destination:=ws.Range("B10").End(xlUp).Offset(1, 0)
End If
Next icell
Application.ScreenUpdating = True
End Sub
Bookmarks