Watch the video below to understand the problem and the solution to find and extract all specific values from your Excel data:

We can view this video on YouTube.

Here’s the complete VBA code:

Sub findAll()
Dim c As Range
Dim findWhat As String
Dim i As Long

i = 2

findWhat = InputBox(“Enter the names you want to find”, “Find…”)
If findWhat = “” Then
MsgBox “You did not enter anything in the inputbox!”
Exit Sub
End If
With Worksheets(1).Range(“A2:A22”)
Range(“C2:C22”).Clear
Set c = .Find(findWhat, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address

Do
Cells(i, 3) = c.Value
Set c = .FindNext(c)
i = i + 1
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End With

End Sub

How does the logic of the above code work:

1. We ask the user what data he would like to find and extract
2. If he doesn’t enter a value in the input box we give out a message to warn him
3. If he enters a value in the input box, we get the first occurrence of the data using the Find method
4. Then we use a ‘do while’ loop and the ‘FindNext’ method to find and extract all the relevant values and place the extracted data in an appropriate column. This might not be possible with a simple Vlookup.
5. The looping process is executed as long as the find result is not blank and not the same as our first result

Further reading:

Using Find and FindNext Methods in VBA