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”)
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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Find and Extract All Specific Values from Excel data”

  1. Thank you for all the support and help during my journey on vba. i am teaching myself and use mostly your videos.

    In a previous video….manage database… there is ‘psave’. I am stuck here, get error all the time. Can you please explain what it is?

    Thank you very much
    Linda

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.