Excel VBA Course
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

Search entire workbook return all results in Excel

0

Dear team,

Thanks for the great job you everyday to help perform in Excel.

I downloaded your Excel file "Search Entire Workbook Return All Results in Excel " a coup, of days ago. That is a very good job done. But there's something I want to know. The macro searches the value in cell C4 and returns it. But it only returns one value in the cell (for example "Jim"); I want to be able to return more than one value in the cell (for example "Jim Carson" or "Jim Carson Luke..."

I implemented the search module as per your guidance, it worked first and then something might have changed because it displays an error (Run-time error 9). I would also like to be able to search a value by typing in upper or lower capitals in the search value cell (Cell C5). I want also the system to display a message when there's no result for the search

Yours faithfully

excel
MS Office Version: 2016
posted by: Chairmanbk (rep: 2) on Sat Mar 27, 2021 at 7:57 am
Answer
Discuss

Discussion

ChairmanBk

I think you updated your question (but I can't really tell how). Please ask a spefic second question (and explain what you did)- rember that other users may be reading your question to see if their problem is the same as the one you had..
John_Ru (rep: 6142) Apr 7, '21 at 1:11 pm
Add to Discussion

Answers

0
Selected Answer

Hello Chairmanbk and welcome to the Forum

Don's tutorial/ macro does that if the monthly data contains names like "Jim Carson Luke" in column B (try using Find/Replace for the whole workbook to swap "Jim for that) and you put (exactly)  "Jim Carson Luke" in cell C4. (You might u9using thev irst characters typed in C4se data validation on C4 if using this "in anger", with several tricky names).name 

In the attached revised file, the macro is tweaked so it makes a partial search o the start of Name (using the first characters typed in C4).  I've replaced Jim with Jim Carson Luke in the Jan data only. I've then changed one line of the code, replacing the line in italics with the one in bold:

            'Check if the value in the row equals our search value
            'If (dataArray(i, searchField) = searchValue) Then
            If searchField = 2 And Left(dataArray(i, searchField), Len(searchvalue)) = searchvalue Then
                'MATCH FOUND! Now do something!
That checks that both Name is the field you're searching on and that the cell starts with what's in C4. That's done by  VBA's Left (<<string>>, <<number of characters>>) which returns the number of characters specified in <number of characters>> e.g. 4. In this case, I use LEN(searchValue) for the number (the length of what's in C4) so it compares the start of string with what's in C4.

A search will now return both Jim Carson Luke rows and Jim rows if you search on Jim. Clear and search on Jim C and only the Jim Carson Luke rows will be displayed.

I've also added an If statement in C5 to give a warning (if E4=Name) that the search isn't an exact one (but is a partial search on the begining only). 

Hope this works for you. 

posted by: John_Ru (rep: 6142) on Sat Mar 27, 2021 at 8:39 am
Discuss

Discussion

Thank you for tour timely reply!
Sorry, I realized that I misexplained my request. In fact I want the macro to return for exemple "Jim Carson..." when I type Jim in cell C4. I don't want to need to type the full name befores it returns it
Chairmanbk (rep: 2) Mar 27, '21 at 9:01 am
See my revised answer and attached file.

Next time, please edit your question as I requested (so other users can see what you wanted - they won't always wade into the Discussion)
John_Ru (rep: 6142) Mar 27, '21 at 9:54 am
I changed my Answer again (so it justs searches for the starting characters e.g. "Jim C" since I think that's what you're seeking (from your comment),.
John_Ru (rep: 6142) Mar 27, '21 at 10:36 am
Dear John,
Thank you very much. It works perfectly! 
Chairmanbk (rep: 2) Mar 27, '21 at 4:51 pm
Glad it worked for you!
John_Ru (rep: 6142) Mar 27, '21 at 5:39 pm
Sorry!
I updated my code per your guidance, it worked the first time, but something might have changed because it displays an error (Run-time error 9). I would also like to be able to search a value by typing in upper or lower capitals in the search value cell (Cell C5). I want also the system to display a message when there's no result for the search
Chairmanbk (rep: 2) Apr 7, '21 at 1:25 pm
A case-insensitive search can be done but please raise a brand new Question (not another discussion point under this one, as I suggested) and post your modified Excel file. Without that, we can't really guess what you've changed.
John_Ru (rep: 6142) Apr 7, '21 at 2:49 pm
The case-insensitive search can be done by converting both the search value and the array values examined to lowercase using the LCase() function. These lines need to change (changes in bold):
searchvalue = LCase(dashboard.Range("C4").Value)
and 
If searchField = 2 And LCase(Left(dataArray(i, searchField), Len(searchvalue))) = searchvalue Then
John_Ru (rep: 6142) Apr 7, '21 at 2:59 pm
Add to Discussion


Answer the Question

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