VBA to search for different value in all worksheets and then write down where they ar

[Shiftyshell]

Hi all,

I am looking for a reasonable solution for searching for various entered values in all worksheets of one Excel file using a table, which would then list on which worksheet the given value is located.

Thank you in advance for any help. :)

[p45cal]

Supply a sample workbook with data in it and a sample of some expected results.

[Shiftyshell]

I share a file, my idea is to have a find panel on the "Summary" tab with a field for entering a search value (for example, a serial number). The output of the search would be a listing where the term is located everywhere:
Example:


Serial number: A000000000
Result: - is located 3x in the worksheet with the word "XXX"

If you have an idea of how this could be done with VBA, I would be grateful.

[p45cal]

I've had to guess what you want in the attached; there is no serial no. A000000000, not even the word Serial on any sheet, nor is there "XXX" anywhere - did you upload the correct sheet?
In your file I found stuff like A1776804000 and A 177 680 40 00 so presumed you want them both found so I removed the spaces from both what is sought and what is searched - this will lead to its own problems possibly.
There's a user-defined function called WhereIsIt that's been used in cells D43 and D44 of the Summary sheet.
I hope it's what you're looking for.

[Shiftyshell]

That's exactly what I needed. Can the function be applied to a UserForm as well?

[p45cal]

Very probably.

[Shiftyshell]

173/5000
















As an example, I created a UserForm that runs when you press the "Find" button. Can your code be applied to this UserForm? Please see the attachment.

Thank you in advanc




As an example, I created a UserForm that runs when you press the "Find" button. Can your code be applied to this UserForm? Please see the attachment.


Thank you in advance for your reply in advance. :rotlaugh: