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

Can a VBA 'Search.Value' macro be made non-exact?

0

This question stems from the guide (https://www.teachexcel.com/excel-tutorial/2110/search-entire-workbook-amp-return-all-results-in-excel-advanced-method?nav=yt)

The above guide shows how to create a macro that takes a search value and populates data based on a Data Array. The macro works brilliantly, however, the search value only works when it is an exact match i.e. search = "Collier St Substation" & data = "Collier St Substation". The macro will not work if you search = "Collier st substation" or just "Collier st".

Is there a way to get the macro to work when the Search.Value is not an exact match?

excel
posted by: CHUMPHREYS on Tue Mar 8, 2022 at 12:58 am
Answer
Discuss

Discussion

Did either of my suggested Answers / files work for you? 
John_Ru (rep: 6142) Mar 10, '22 at 12:44 pm
That's a "no" then! Haha! 
John_Ru (rep: 6142) Mar 14, '22 at 4:14 pm
Add to Discussion

Answers

0

Hi and welcome to the Forum.

You seem to be asking if Don's macro can be modified to do a partial search (i.e. part of the cell contents). I answered a very similar Forum question about a year ago - see Search entire workbook return all results in Excel. You'll see that the file attached to that answer returns results where the "Name starts with" the search value (e.g. like your Collier example).

VBA can do more sophisticated searches than that (e.g. see my answer Fast Search Multiple Workbooks with Wilcard) but I hope this helps. Don't forget to return here to comment/ select the answer if appropriate.

posted by: John_Ru (rep: 6142) on Tue Mar 8, 2022 at 3:33 am
Discuss


Answer the Question

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