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

Is one able to searchField(s) based on two different criteria (exact and partial values).

0

First thank you for your assistance, I have gone through the forum and adapted my code thus far with your suggestions on the Excel Macro that Searches Entire Workbook and Returns All Matches.

I was wondering if you could suggest a solution for two questions.

My 1st Question: Is one able to searchField(s) based on two different criteria (exact and partial values).

I have Four searchField(s) presented on the Dashboard page with the corresponding Dropdown List.

Two searchField(s) for Numerical IDs and Barcodes: 
When searched using this portion of the code ---> [If (dataArray(i, searchField) = searchValue) Then] it worked great, returning to the Dashboard page, exact values.

Then, I have Two additional searchField(s) one for Names and the other Titles.  Here it would be best to have partial names/titles returned onto the same Dashboard page.

When searched using his portion of the code---> [If searchField = 8 And Left(dataArray(i, searchField), Len(searchvalue)) = searchvalue Then] this worked great, returning to the Dashboard Page partial names.  But I wasn't sure how to adapt the VBA code to do this as well when selecting Titles (o/k/a searchField = 9) as well.

Leading to my 2nd Question: Is one able to have multiple searchField(s) return 'partial' resluts to the Dashboard page?

excel
posted by: Streetsteps (rep: 2) on Tue Apr 5, 2022 at 4:05 pm
Answer
Discuss

Discussion

Hi Streetsteps and welcome to the Forum

It's certainly possible to or use multiple criteria and to get "partial results" (e.g. my previous Forum answer here: Fast Search Multiple Workbooks with Wilcard) but for a full answer, we need the full code you're using and preferably the file you want to search...

Please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing macro and data. Then we should be able to give specific help.
John_Ru (rep: 6142) Apr 6, '22 at 9:47 am
Streetsteps- if you can't help yourself (by responding to my question / revising yours), we really can't help you, sorry.
John_Ru (rep: 6142) Apr 26, '22 at 9:22 am
Uhm, just so you are aware, I did revise my answer by, editing my question and uploading the file -- as you instructed below.

Please edit your original question and use the Add Files... button to upload a representative Excel file 
Streetsteps (rep: 2) May 17, '22 at 4:37 pm
Streetsteps.

Sorry but I didn't see that (but responded when I saw an Alert today, about your comment above). I check the webite at least once daily so I'm a bit surprised.

It seems that the website email alerting is not working (again) so I'll let Doin know, so it can be fixed. I suspect that several users aren't seeing my Answers as a result. That or they don't feel they have to acknowledge my effort (it's pretty common, sadly)
John_Ru (rep: 6142) May 18, '22 at 10:41 am
Add to Discussion

Answers

0
Selected Answer

Streetsteps.

If you want to search full values or partial values (based on the first few characters),  you can just replace the line:

If (dataArray(i, searchField) = searchValue) Then

with:

If Left(dataArray(i, searchField), Len(searchvalue)) = searchvalue Then

without stating which search field is used since it will also work when all digits of a Barcode are entered (say). Also you can do a partial search for that choice too. I can't really check with your file since I can't open the  open links involved i.e. Important Charts, Master Chart.xls, UMLE Reports, UMLE Rights Research Chart.xlsm.

As I said before, for multiple criteria and to get "partial results", see my previous Forum answer here:  Fast Search Multiple Workbooks with Wilcard) 

Also, you might be interested in correcting the foramt of the UPC in your Dashboard worksheet. To do that, Highlight column K. Right click then  pick Format cells.../Custom and overtype "General" (under Type:) with "############" (ignoring quotation marks). You'll then see the full 12-digit number displayed e.g. 726417361721 rather than engineering notation like 7.26417E+11.

Hope this helps 

posted by: John_Ru (rep: 6142) on Wed May 18, 2022 at 10:38 am
Discuss

Discussion

Thank John_Ru, I understand, you are providing quite a service to all of us.
I appreciate your advice and guidance on this project.

Moreover, I disable the open links, on my main template, and appreciate the suggestion of formatting the UPC in the Dashboard Sheet, it was driving me a little crazy constantly formatting the numbers cell, when the results came back.
Streetsteps (rep: 2) May 18, '22 at 6:06 pm
Thanks for your kind comnents., Streetsteps.

Not sure what you meant about disabling links but hope you have a working sokution.

I just revised my Answer to restore the hyperlink to my previous related Answer.

Hope you like working in the music industry!
John_Ru (rep: 6142) May 18, '22 at 6:18 pm
Add to Discussion


Answer the Question

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