You can't use that original formula to do that, but since you're using VBA anyway, you can use a Do Loop to find all the instances of that code and insert them into the results.
Sub Search1()
Dim cell As Range, dd As Range, ddFIRST As Range
Dim aa As String, ee As Long, Rw As Long
aa = Sheets("Search").Range("C4").Value
If aa = "" Then Exit Sub
Sheets("Search").Unprotect 'Password:="hi"
With Sheets("STS")
On Error Resume Next
Set dd = .Columns(3).Find(What:=aa, After:=.Cells(4, 3), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If dd Is Nothing Then
If MsgBox("Is the correct Part Number (" & aa & ") entered?", vbYesNo) = vbYes Then
ee = Application.InputBox("Please enter Vendor Code")
If ee <> 0 Then
Range("C6").Value = ee
Else
Range("C4").ClearContents
End If
Set ddFIRST = dd
Rw = 9
Do
Range("A" & Rw).Value = dd.Value
Range("C" & Rw).Value = dd.Offset(, 1).Value
Rw = Rw + 2
Set dd = .Columns(3).FindNext(dd)
Loop Until dd.Address = ddFIRST.Address
Else
Range("C1:D1").ClearContents
End If
'If ee <> 0 Then Range("C6") = ee Else Range("C6") = ""
Sheets("Search").Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End With
End Sub
Bookmarks