I am sure you have received a huge table of data in excel and have to search for a few keywords (not just one). As an example, let’s say you receive a huge list of employee names, ids etc from HR of employees who need to complete a particular training. You want to identify if any of your team members is on that list. Even with a small 10 member team, searching becomes a chore. Here is how to create a simple macro that can be invoked anytime to perform a looped search.
Open a new excel workbook. Save the workbook as an
Excel Macro-Enabled Workbook. The workbook will get saved with a .xlsm extension. Press
Alt+F11 to open the VBA IDE. You will see the Project Explorer to the left. Right click on
Microsoft Excel Objects, choose
Insert from the menu and select
Module. We will be adding our code to this module
Now back to the excel sheet (grid). There is not much setup to do here; Our macro will work with any selection irrespective of the column/row. In any case, if you want to format and beautify this, go ahead and do so.
On to coding. Double click the Module (most likely called Module1) and add the following code
Option Explicit ' MACRO to perform looped find Public Sub LoopedFind() ' How to use '------------ ' 1> Select the range of cells which need to be searched ' 2> Invoke the LoopedFind macro (Alt+F8 -> Select LoopedFind) ' 3> Navigate to the folder where the excel sheet to be searched is located ' 4> Results will appear as new columns in the same sheet Dim FileName As Variant Dim SrcWkbook As Workbook Dim SrcWksheet As Worksheet Dim DestWksheet As Worksheet Dim RangeSource As Range Dim Cell As Range Dim FoundCell As Range Dim SrcFilePath As String Dim SrcFileName As String Dim SrcWksheetName As String Dim RowNum As Long Dim lColumn As Long Set RangeSource = Application.Selection Set DestWksheet = ActiveWorkbook.ActiveSheet ' Show File Open window with only xls files. If you are ' looking for .xls (not the newer fancy .xlsx ones) ' changes below FileName = Application.GetOpenFilename( _ FileFilter:="Excel Files (*.xlsx), *.xlsx", _ FilterIndex:=1, _ Title:="Select a Workbook") If FileName = False Then Exit Sub Application.DisplayStatusBar = True Application.StatusBar = "Macro started" Set SrcWkbook = Workbooks.Open(FileName:=FileName) For Each SrcWksheet In SrcWkbook.Sheets SrcFilePath = SrcWkbook.Path SrcFileName = SrcWkbook.Name SrcWksheetName = SrcWksheet.Name For Each Cell In RangeSource ' Identify the last column with data for the current row lColumn = DestWksheet.Cells(Cell.Row, Columns.Count).End(xlToLeft).Column + 1 Application.StatusBar = "Currently Finding : " & Cell.Value & " in " & SrcWksheet.Name Set FoundCell = SrcWksheet.Cells.Find(what:=Cell.Value, _ LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) If Not FoundCell Is Nothing Then RowNum = FoundCell.Row DestWksheet.Cells(Cell.Row, lColumn).Value = "› " & SrcWksheetName & " » " & FoundCell.Address End If Set FoundCell = Nothing Next Cell Next SrcWksheet SrcWkbook.Close Savechanges:=False Application.StatusBar = False End Sub
I won’t go too much into details, but here is the basic algorithm.
- Allow user to select the file which needs to be searched in (your haystack basically)
- Loop through each sheet in the user selected workbook
- For each cell in the selected source, find if the value exists in the sheet of the opened workbook
- If match found, populate the next available column beside the cell value being searched (needle) with the worksheet name and cell address
Save the workbook. Add some data to test. I have put in names of some cricketers in my workbook like the image to the right. To search, I select the data and press
Alt+F8. This brings the list of available macros. Once you choose
"LoopedFind", the code begins executing and you can choose the file with the large amount of data. The final results are here to see.
DestWksheet.Cells(Cell.Row, lColumn).Value = "› " & SrcWksheetName & " » " & FoundCell.Address
This line above is the code that outputs the worksheet name and address. However, you can change this to suit your purpose. If you need a specific data retrieved you can manipulate FoundCell.Address to reference another column in the row where data is found and get its value. (Somewhat like a Dynamic VLOOKUP that picks up the filename at runtime)
I wish I had taken some effort to put this piece of code earlier. Not just with names, ids, I see this sheet being useful in a number of places. For one, whenever we work on a release in the mainframe environment, we create an impact analysis report. It is usually a mammoth sheet with the list of all copybook changes and the impacted programs. Out of the 1000 rows, my team would be responsible for a smaller fraction of programs. Instead of manually checking my list of programs against the IAR, I could perform this looped find and quickly know how many my team’s programs are impacted.
Hope you found this useful. Share your thoughts and feedback through the comments section.