This is a follow up to the tutorial "Fast Search Entire Excel Workbook & Return All Results" (which was fabulous, btw). Is there a way to amend the VBA to search any field for the value entered into C3/Search value?
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.)
Amend VBA to search any field in a workbook
0
Discussion
Hello AnneD and welcome to the forum.
Altering code to return different results is definitely possible. It all comes down to being clear about what to search for and what results to return.
The existing code in the tutorial workbook will search for an ID number or a Name and return all data for Region 1 thru Region 11 from all worksheets for that ID or Name.
Are you wanting to enter a value in "C4" (ex 18508) and in "E4" specify a region to search (ex Region 5) and have the code return all results that match that combination? (each result would be listed under the specified region and indicate the ID and Name) Or do you want results from all regions?
Please clarify and be specific as possible as to what you want to search for and the results you want returned.
Cheers :-)
Altering code to return different results is definitely possible. It all comes down to being clear about what to search for and what results to return.
The existing code in the tutorial workbook will search for an ID number or a Name and return all data for Region 1 thru Region 11 from all worksheets for that ID or Name.
Are you wanting to enter a value in "C4" (ex 18508) and in "E4" specify a region to search (ex Region 5) and have the code return all results that match that combination? (each result would be listed under the specified region and indicate the ID and Name) Or do you want results from all regions?
Please clarify and be specific as possible as to what you want to search for and the results you want returned.
Cheers :-)
WillieD24 (rep: 687) May 9, '25 at 11:26 am
Hello! What I'm trying to do is enter a value in the Search field, e.g. "ABCDEF" and the results would pull in any instance of that string no matter the column name, so pulling in results from all fields.
ABCDEF could appear in Column A, Column B or Column C. If the user enters ABCDEF into the search value, the results would return rows where that value appeared in any of those 3 columns. Also, additional columns may be added to the worksheet so I also want to eliminate any maintenance needs with adding the field value to the VBA itself.
Basically, I'm looking to eliminate the need for the users to know which field they are supposed to be searching in.
ABCDEF could appear in Column A, Column B or Column C. If the user enters ABCDEF into the search value, the results would return rows where that value appeared in any of those 3 columns. Also, additional columns may be added to the worksheet so I also want to eliminate any maintenance needs with adding the field value to the VBA itself.
Basically, I'm looking to eliminate the need for the users to know which field they are supposed to be searching in.
AnneD (rep: 2) May 9, '25 at 12:11 pm
Hi Anne. I read your correspondence with Willie (above) and I'm sure he'll be able to help you so will step back.*
I'm not clear- are you looking for a match with the entire search value (e.g. cell= "ABCDEF") or a partial matches too (e.g. including a cell containing ""6789ABCDEFGHIL" say)
* If for some reason he can't, I can but not immediately -I've modified that VBA code for several earlier Questions here..
I'm not clear- are you looking for a match with the entire search value (e.g. cell= "ABCDEF") or a partial matches too (e.g. including a cell containing ""6789ABCDEFGHIL" say)
* If for some reason he can't, I can but not immediately -I've modified that VBA code for several earlier Questions here..
John_Ru (rep: 6722) May 9, '25 at 3:41 pm
Answers
0
Selected Answer
Hello AnneD,
May 12 - attached file updated (Rev-2)
In the attached file I have written some new code which I think does what you are looking to do (if I understand you correctly)
Here's how it works:
On the "Dashboard 2" sheet, in cell "C4" enter whatever it is you want to search for. It can be any value from any column - an ID, a Name, or a number. Click the "Search" button and it will populate the "Dashboard 2" with all results from all worksheets. There is COnditional Formatting so where the matches are found the cell is highlighted. And, of course, clicking "Clear" deletes all data. This code also allows for changing the number of columns on the sheets (add / delete) without having to modify the code. Also, the number of columns on each sheet can be different.
If this solves things please mark my answer as Selected.
If you were hoping for something else let us know and we will work on it.
Cheers :-)
Discussion
Hello! In the example you provided it works absolutely perfectly! However, I noticed in the example that the individual worksheets need to be listed in the array. My workbook will have worksheets added over time and I'd rather not have to update the vba each time. Is there an easy way to search all worksheets except the dashboard? I think the original version of the code did something similar but it was part of an if/then statement.
Question: how does it allow for changing the number of columns? my worksheet has 24 columns and the search isn't pulling in results from the last columns.
thanks so much for your help so far! We are so close!
Question: how does it allow for changing the number of columns? my worksheet has 24 columns and the search isn't pulling in results from the last columns.
thanks so much for your help so far! We are so close!
AnneD (rep: 2) May 12, '25 at 8:09 am
Ah...hang on... I think I may have figured part of it out. While search does in fact look through any column in a worksheet, what is hardcoded is the dashboard column: so in your example, if I had data that was in column Z in a worksheet, it wouldn't appear in the dashboard unless I had updated the code to display up to column Z!
I think the only thing I'll need help with then, is updating the search to look at any worksheet except the dashboard.
I think the only thing I'll need help with then, is updating the search to look at any worksheet except the dashboard.
AnneD (rep: 2) May 12, '25 at 10:25 am
Hi Anne,
Sorry for my oversight. Only small changes were needed.
The line:
Needed to be:
Note: add the line "Skip:" before the line "Next ws"
And the line: (my mistake)
Should heve been:
The revised file ( Rev-2 ) is attached above.
Note: In Row1 of the data sheets (Jan, Feb, Mar, etc) there can be no blank cells.
I think I have got this how you need it.
If so, please mark my answer as Selected
Cheers :-)
Sorry for my oversight. Only small changes were needed.
The line:
For Each ws In Sheets(Array("Jan", "Feb", "Mar", "Apr"))Needed to be:
For Each ws In Worksheets
If ws.Name = "Dashboard" Or ws.Name = "Dashboard 2" Then GoTo SkipNote: add the line "Skip:" before the line "Next ws"
And the line: (my mistake)
Worksheets(wsName).Range("A" & Rw & ":M" & Rw).CopyShould heve been:
Worksheets(wsName).Range("A" & Rw & ":" & LCletter & Rw).CopyThe revised file ( Rev-2 ) is attached above.
Note: In Row1 of the data sheets (Jan, Feb, Mar, etc) there can be no blank cells.
I think I have got this how you need it.
If so, please mark my answer as Selected
Cheers :-)
WillieD24 (rep: 687) May 12, '25 at 11:24 am
Hi Anne,
(I didn't see your added comment until after I added my post and updated the file Rev-2)
You are close about the column situation. The number of columns on each sheet can be different. What is critical is that there are no blank cells in Row 1. If there is a blank cell the code will not detect the columns after the blank cell. It's OK to not have any data in a particular column, just make sure the cell in Row1 is not blank.
Cheers :-)
(I didn't see your added comment until after I added my post and updated the file Rev-2)
You are close about the column situation. The number of columns on each sheet can be different. What is critical is that there are no blank cells in Row 1. If there is a blank cell the code will not detect the columns after the blank cell. It's OK to not have any data in a particular column, just make sure the cell in Row1 is not blank.
Cheers :-)
WillieD24 (rep: 687) May 12, '25 at 11:34 am
Perfect! I can't thank you enough for your help!
AnneD (rep: 2) May 12, '25 at 2:32 pm
@AnneD
Glad I was able to help you and Thanks for selecting my answer.
Cheers ;-)
Glad I was able to help you and Thanks for selecting my answer.
Cheers ;-)
WillieD24 (rep: 687) May 12, '25 at 6:27 pm
Your workbook makes an elementary design mistake. When working in Excel all data should be in one sheet, then creating reports is so much simpler. In your casw I would probably suggest automating AdvancedFilter preferably after combining all the data.
royC (rep: 10) May 20, '25 at 7:28 am
@royC
The workbook I provided was not intended to be the final version (information provided was just a sample - not the actual final workbook) What I provided was just the concept and code so AnneD could implement it in her actual workbook. No filtering was implemented because it was not asked for.
Before criticizing you should better analyze and comprehend the information.
The workbook I provided was not intended to be the final version (information provided was just a sample - not the actual final workbook) What I provided was just the concept and code so AnneD could implement it in her actual workbook. No filtering was implemented because it was not asked for.
Before criticizing you should better analyze and comprehend the information.
WillieD24 (rep: 687) May 20, '25 at 10:42 am