vba find next search value based on input box

[oloyejawnson]

Hello Fam,

I have a workbook containing a worksheet with a list of details and I have managed to put together a VB code shown below that finds the first match for the input box value but I need it to give 'next' option on the message box with the result to find the next match if there is one. For instance, if the word "index" occurs 3 times I'd like to be able to click next for it to go to the next match for the word "index". The search range is on column B on worksheet "Main" in the attached file (Dashboard X)

Please help!

Please Login or Register  to view this content.

[CK76]

Instead of using WorksheetFunction to find match. Try using Range.Find method.

Something like below...

Please Login or Register  to view this content.

[oloyejawnson]

Thanks a bunch CK76.. but using range.find looks for an exact match but I want the code to search for any word/string with the input provided. for instance, If I input the word "index" I want it to display details for 'account index' followed by 'fee index' and so on, if applicable hence the use of worksheet-function 'index' and 'match' as shown below:

Please Login or Register  to view this content.

If there's a shorter line of code I won't mind..

[CK76]

Did you actually run the code or use the workbook I uploaded?

Range.Find() without argument for "LookAt:=" parameter, will default to xlPart.
Meaning it will find partial match (i.e. if you use "index" it will show detail for "Account Index", and then "Fee Index").

Try it on the workbook I uploaded.

[oloyejawnson]

Yes I ran the code you uploaded and using "index" shows 'Solution Not Present in the table'

[CK76]

? It shows following on my end.

With "index" without quotes in inputbox...
0.JPG

Next is...
1.JPG

Try adding the parameter "LookAt:="

Please Login or Register  to view this content.

[jolivanes]

From Ozgrid 49668:
The settings LookIn, LookAt and SearchOrder are saved each time the Find Method is used.
For this reason one should always specify these settings explicitly each and every time you use the Find Method.
If you don't, you run the risk of using the Find Method with settings you were not aware of.

[CK76]

Ah, good to know. I've never used anything other than partial match and had never encountered that issue.

Thanks for the heads up

[jolivanes]

We seem to learn all these things through trial and error.

[oloyejawnson]

Wow! Nice work CK76.. you really got me through this! Thanks for the tip as well jolivanes. appreciate y'all!