Making use of VBA To Search Across Many Worksheets For A Textual content String

Posted in WordPress on May 21, 2014

While Excel is capable of dealing with substantial amounts of details, occasionally that can generate a difficulty. What happens when you need to find details, but it could be in any of the worksheets in the present file you are doing work on?

Excel has some excellent lookup capabilities but this write-up will show you how some uncomplicated VBA code can look for an full Excel application, and then accessibility associated cells for other details.

In our illustration, we’ll be looking throughout several worksheets for a identify, and then return the knowledge in an adjacent mobile for just about every look for outcome.

Looking Multiple Worksheets

As soon as you’ve prepared the code to lookup one particular worksheet for your text string, it truly is just a make any difference of repeating the code for each individual sheet in the workbook.

If you duplicate the next knowledge into a few different worksheets named “example1”, “example2” and “example3” we are going to create some code to search in each sheet and glimpse for the name “Mary”.

Names
John
Maria
Henri
Mary
Peter
Jacques
Mary

Very first, we are going to publish some code to search a solitary worksheet. The code produces a new selection and when it finds an event of the title, adds the cell tackle to the assortment. Take note that we also add the worksheet name to the address.

Dim myItems As New Selection
Established r = ActiveCell.CurrentRegion.Columns(one)
searchStr = “Mary”

For x = two To r.Rows.Depend
If InStr(r.Rows(x), searchStr) Then
myAddress = “‘” & r.Worksheet.Title & “‘!” & r.Rows(x).Deal with
myItems.Insert myAddress
End If
Upcoming

With just about every occurrence of the title included to the assortment, we can now print the collection of cell addresses to take a look at the code.

For Every single n In myItems
Debug.Print n
Following

Mainly because we have to have to extend the look for across many sheets we merely enclose the code over in a loop of each individual worksheet in the present application. For every single worksheet we activate and set the variety to the initial column of data.

For Just about every s In Worksheets
s.Activate
Array(“a1”).Activate
Established r = ActiveCell.CurrentRegion.Columns(one)
For x = 2 To r.Rows.Rely
If InStr(r.Rows(x), searchStr) Then
myAddress = “‘” & r.Worksheet.Name & “‘!” & r.Rows(x).Handle
myItems.Include myAddress
Conclude If
Subsequent
following

Based on your details, if you print out the collection of cell addresses, it really should appear a little something like this:

‘example1’!$A$5
‘example1’!$A$8
‘example2’!$A$5
‘example2’!$A$eight
‘example3’!$A$5
‘example3’!$3$eight

Due to the fact you now have a selection of the cell addresses, you can now adapt the final result to go well with your requires. Most likely column 2 has the telephone range or electronic mail address.

To access the adjacent column for every single deal with, you could use this code:

For Every single n In myItems
Debug.Print vary(n).offset(,1)
Future

Summary
Whilst Excel has built-in functions to look for an whole workbook, occasionally you require a very little much more overall flexibility. With VBA, you can make practical features that fulfill your have needs and targets for your knowledge.

By Andy L Gibson

Leave a Reply

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