Find and Extract All Specific Values from Excel data

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”)
Set c = .Find(findWhat, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address

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