Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Adding another search pair to Fast Search Entire Excel Workbook & Return All Results into a Dashboard


How do i complete 2 searches simultaneously. ? 

I have attached the file and to show the requirement  

(File 1)

As C4 and E5 are the target box. What if theres another target box along with it say 

C4 AND E4 

C5 AND E5 

and more if required for combined search 



Selected Answer


In the revised sheet attached I've modified the code from Don's tutorial (already modified to give vertical headers following your previous question), I've taken the simple approach of replicating two portions of Don's code to define second variables (searchValue2 + fieldValue2) and then to get the array index to match those. Then it's a simple matter of changing the test (for matching values) to OR together the existing test with a check if  the entry in position (i, searchField2) of dataArray matches searchValue2. Changes are shown in bold inthe code extract below (full code in the file again):

Sub Data_Search()
' TeachExcel.com
.... << code unchanged>>
'Get user input
searchValue = dashboard.Range("C4").Value
fieldValue = dashboard.Range("E4").Value
searchValue2 = dashboard.Range("C5").Value ' extra search value
fieldValue2 = dashboard.Range("E5").Value ' extra field definition

'Clear Dashboard
Call Clear_Data

'Figure out by which field we will search.
If (fieldValue = "ID") Then
    searchField = 1
ElseIf (fieldValue = "Name") Then
    searchField = 2
End If
'repeat for second parameters
If (fieldValue2 = "ID") Then
    searchField2 = 1
ElseIf (fieldValue2 = "Name") Then
    searchField2 = 2
End If
.... << code unchanged>>

            'Check if the value in the row equals our search value
            If (dataArray(i, searchField) = searchValue) Or (dataArray(i, searchField2) = searchValue2) Then
.... << code unchanged>>
End Sub
This should work well for two search pairs but, if you really need multple search pairs, a  different approach is probably needed for code efficiency/ user experience (since it may become difficult for the user to differentiate why a given record is in the search results).

REVISION: Where large sets of data are used, the Transpose function may produce an error (where a sheet has null values or the search produces more than about 5k cells). In the revised sheet attached, Transpose is NOT used since the population of the data array is altered to anticipate horizontal data display. Note however that displaying a large amount of data horizontally (with vertical headers) will trigger an error if a dimension of the sheet (on an attempted copy) might exceed the maximum sheet size of 1,048,576 rows by 16,384 columns in versions of Excel after 2010 (cells beyond those limits just don't exist!). See Microsoft's guidance here: Excel specifications and limits.

The second file below (...with sheet names v0_c.xlsm) also adds the sheet name (from which a search column was obtained) in an additional row so you can better see where results are from. That row is in italics, with the Clear_Data macro modified to use .ClearContents (rather than .Clear) so that formatting remains. In this version, when each sheet if searched, the output array in limited by this additional test:

If nextColumn + LastRow < 16384 Then
    'Transpose and increase size of array that will hold the data to display to its max possible size for the current worksheet.
    ReDim datatoShowArray(1 To UBound(dataArray, 2) + 1, 1 To UBound(dataArray, 1) + 1)
    'Limit size of array  to its max possible size for Excel
    ReDim datatoShowArray(1 To UBound(dataArray, 2) + 1, 1 To (16384 - nextColumn))
End If
and if the actual search results would "break" Excel, it is stopped by this extra test within the worksheet search loop:
If j + nextColumn > 16384 Then
    MsgBox "Unable to report sheet " & ws.Name & "- too many columns for Excel!"
    Exit Sub
End If
The first sheet has 100,000 rows and will work with high numbers in an ID search (as sent)

Hope this works well for you. 



p.s. Please change your question title to differentiate it from your previous one (for the guidance of other users), e.g. "Adding another search pair to Fast Search Entire Excel Workbook & Return All Results into a Dashboard"  
John_Ru (rep: 2867) Dec 27, '21 at 6:05 am
Nigel. Thanks for selecting my answer and for following my suggested title change.
John_Ru (rep: 2867) Dec 27, '21 at 10:25 am
great thanks it worked. How did you restrict the search button to one click? 
Nigel (rep: 6) Dec 27, '21 at 11:43 am
The search button runs the modified Data_Search macro (which now does both searches at the same time, as I explained)
John_Ru (rep: 2867) Dec 27, '21 at 11:45 am
No what I meant is (not related to the above code changes), how did you restrict the assign macro (button) to a single click and not multiple clicks 
Nigel (rep: 6) Dec 27, '21 at 11:54 am
Like I tried to replicate this code in a new excel. But when I assigned the macro to the search button, I can click search button multiple times with same entry populating again and again. Its not restricting it to just one click.
Nigel (rep: 6) Dec 27, '21 at 12:04 pm
Not sure I follow but if the search repeats, it sounds like the line
Call Clear_Data
is absent or you did not add the macro Clear_Data to your new file. Can't tell without seeing your file! 
John_Ru (rep: 2867) Dec 27, '21 at 12:29 pm
I have attached an excel "Search Entire Workbook Return All Results but with Vertical headers v0 a 1 .xlsm". Here in Jan sheet have updated row count upto 20K (assuming there are lacs of data that needs to be search through). So if I search an ID say 20288, its throwing an 1004 error. 

Is the code restricting the serarch to a particular number?

If the rows are reduced, it searchs through the data.
Nigel (rep: 6) Dec 28, '21 at 12:05 am
Nigel. You didn't say originally that you wanted to work with large amounts of data (so I took a short cut) - the Transpose function has a limit of about 5k cells (and no nulls) as I recall. 

That line needs to be replaced with a  2D loop to swap array values (or the method of populating of the array changed). Suggest you look in the Tutorials section or search elsewhere for code to do that (I. have no time today to correct the code) 
John_Ru (rep: 2867) Dec 28, '21 at 2:16 am
Yes I know I didn't mention it before, coz I was only just trying to check what if there are large data that needs searching.  could you please assist when you have the time to re-work on the code. It will be really a big help. 
Nigel (rep: 6) Dec 28, '21 at 2:23 am
Nigel, displaying a large amount of data horizontally (with vertical headers) will trigger an error if a dimension of the sheet (on an attempted copy) might exceed the maximum sheet size of 1,048,576 rows by 16,384 columns in versions of Excel after 2010 (cells beyond those limits just don't exist!). See Microsoft's guidance here: Excel specifications and limits.

Transposing your large vertical data sets (lots of rows) is likely to lead to problems. For that reason you might be better sticking with Don's original method (delivering vertical data from vertical data sources) plus you can filter the results (though the new FILTER array function in Excel365 allows you to filter horizontal data).
John_Ru (rep: 2867) Dec 29, '21 at 4:43 am
Understood. What I meant was , I would about 500k rows and 22 columns that needed work . I work on 2013 excel
Nigel (rep: 6) Dec 29, '21 at 5:16 am
See my revised Answer/ attached file
John_Ru (rep: 2867) Dec 29, '21 at 5:20 am
Okay will review . Just clarifying what I meant , my search is unique values so I will not have more than 1 value when I search. So tha data will not exceed One column and 22 rows when transposed . So if I search a number 123, the master data will just have one entry of 123 . Unlike this tutorial where there are multiple JIm or 112 ID for eg
Nigel (rep: 6) Dec 29, '21 at 5:55 am
In which case the file I sent (and code) should work, once you modify the parameters for your data dimensions and dashboard layout.
John_Ru (rep: 2867) Dec 29, '21 at 6:04 am
With a bit of thought (I gave it none!) you could report the sheet name with the values too. But that's another question...
John_Ru (rep: 2867) Dec 29, '21 at 6:05 am
Okay thanks John. Will review the file and get back to you in case for any doubts. Thanks for your help and guidance, much appreciated 
Nigel (rep: 6) Dec 29, '21 at 6:07 am
File (as sent) is set up with a search- just click the Search button (but note there are lots of "bob" entires returned before any 112 results appear!). Good luck
John_Ru (rep: 2867) Dec 29, '21 at 6:46 am
Nigel, I had a few spare minutes so did a version of the file with tells you the sheet from which each column is from- see Answer, second file. Hope this helps you.
John_Ru (rep: 2867) Dec 29, '21 at 8:27 am
Hi Jon - I cant find the second file. 
Also whats the max number of rows that can be a part of the data (column fixed at 22)? 
If I update same contents till row 50,000, its still not searching?
My values are unique, so there will not be multiple search
Nigel (rep: 6) Dec 29, '21 at 10:49 am
Sorry Nigel, I thought I'd attached the second file. I won't be home for a few hours but will attach then.

You can use many more than 22 columns in your data but need to set parameters to suit. If you don't need second pair, just set those cell to equal the first t set. 
John_Ru (rep: 2867) Dec 29, '21 at 11:45 am
No worries , you can send it when you can .thanks.
so the Jan sheet has about 8133 rows with entries as per the attached file. I extended the rows from 8133 to 50000 (to extend the base). So now if I search for 50000, it throws an error
Nigel (rep: 6) Dec 29, '21 at 11:52 am
When you try, add about 100k entries and try ssearching any entry beyond say 50k - (ID - 50000) , it would throw an error
Nigel (rep: 6) Dec 29, '21 at 11:56 am
You're asking a little much there (testing with 100k rows) Nigel in my opinion... 

In this Q&A Forum, someone asks a question and another person answers (for free). The person with the problem should then spend time seeing if the answer solves their problem (which they may be being paid to do!). No one is obliged to answer a question or deal with the ramifications.

That said, I might try in an hour or so, depending on how my personal life goes. 
John_Ru (rep: 2867) Dec 29, '21 at 2:54 pm
Hi John, I just gave an example.
All I was trying to point out is that , it isn't searching beyond a particular limit.
Nigel (rep: 6) Dec 29, '21 at 3:56 pm
Nigel. Please see second file (now attached to Answer) where I've tested with 100,000 rows in the Jan sheet. It will search all rows but quit if the results would exceed the max columns in Dashboard. Note that the determintation of nextColumn is moved up within macro (to save repetition).
John_Ru (rep: 2867) Dec 29, '21 at 5:37 pm
Thanks John. It's now working as expected. Sorry for the trouble but thanks for your help and picking up doubts with clarity and easy to understand codes/description.
Nigel (rep: 6) Dec 30, '21 at 1:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login