Find them and Extract them – VBA Macro

Posted on by

I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said, “ministry of MAGIC!!!”). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”

I explained him how to do this while chewing mouthfuls of rice & veggies.

But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.

find-and-extract-smith-v4

So here we go.

How to find and extract all matching values

Let’s say you have some data in a range like this.

find-and-extract-data

And you want to find all cells with comp in them. If the values are all in one column, you could use auto-filter to quickly filter cells with comp in them and copy paste them to a target range.You can even automate the steps a bit with advanced filter. 

But what if the data can be in any column?

We can use Find (Ctrl+F) to find the values and click on “Find all” to see all results in the find box. But to extract them, we must take the red pill and escape the limitations of Excel to enter in to the exciting world of VBA.

Here is a quick demo of what our find and extract macro does.

find-extract-all-macro-demo

Here is the code: 


Sub findAll()
    Dim findWhat As String, address As String
    Dim fsr As Range, rs As Range, fCount As Long
    
    findWhat = InputBox("Enter what you want to find?", "Find what...")
    
    If Len(findWhat) > 0 Then
        clearFinds
        Set frs = Range("b4").CurrentRegion
        Set rs = frs.Find(What:=findWhat)
        If Not rs Is Nothing Then
            address = rs.address
            Do
                Range("I5").Offset(fCount).Value = rs.Value
                Range("J5").Offset(fCount).Value = rs.address
                Set rs = frs.FindNext(rs)
                fCount = fCount + 1
            Loop While Not rs Is Nothing And rs.address <> address
        End If
    End If
End Sub

How does it work?

The code is inspired from Bill Jelen’s excellent example on Find method on MSDN.

The logic goes like this.

  1. We start by asking the user what they want to find and store this in findWhat string variable.
  2. If the string to find is not empty,
  3. We clear any previous find results
  4. We grab the current region for cell B4 (change this to the top-left of your find range)
  5. We look for findWhat in this range using range.Find method
  6. As long as Find result is not empty and not same as the first result
    1. We copy the value & address to I5 (change this to target range as per your workbook setup)

Download the Find and Extract workbook

Click here to download the example workbook. Play with the macro to learn its inner workings.

The rabbit hole is deep, don’t stop just here…

If you enjoyed this little macro, you are going to love VBA. Check out our free starter tutorial or extensive VBA section for more.

How would you find and extract results?

I thought the Find method approach would be slow, but I am surprised to see that on a medium sized dataset (12000 values), the macro produced results almost instantly. So I would be using it more often to iterate thru a range to find a value.

What about you? Do you have such problems at work? Do you use VBA to solve them or just ask colleagues during lunch break and hope for a miracle? Please share your approach in the comments.