Find Macro With Userform - To find a data across multiple tabs in excel

[Testify]

Hi Guys - I need help on making a Find Macro with Userform. I tried recording a macro but it won't give me the result I needed. Just need to find a data across 399 tabs in 1 speadsheet.

Thanks!

[Mordred]

Hi Testify, you are going to have to explain your problem a lot better than that. Firstly, what are you trying to find? Secondly, when you say 399 tabs in 1 spreadsheet, do you mean 399 cells? If so, what cell range are they in?

[Testify]

Hi Testify, you are going to have to explain your problem a lot better than that. Firstly, what are you trying to find? Secondly, when you say 399 tabs in 1 spreadsheet, do you mean 399 cells? If so, what cell range are they in?

Thank you for replying. Please see details below:

1. 399 Tabs (sheets) in one excel file.
2. I need to be able to find any data - I guess what would wanna do is use the find function (cntrl+f), to find a data that is located anywhere within the 399 tabs (sheets).
3. I need to be able to do this using a USERFORM.

Hope it answer questions.

[Mordred]

Hi Testify, here is a snippet of code that uses an inputbox but can be expanded into a userform. I'm not showing you a userform with this because I have no idea what your userform looks like. If you want something specific you'll have to upload a workbook. Otherwise, try adapting the following code into a userform

Sub FindOnSheets()
    Dim Ws As Worksheet
    Dim Rng As Range
    Dim FndStrg As String
    FndStrg = InputBox("Find What?", "Search")
    For Each Ws In Worksheets
        Set Rng = Ws.UsedRange.Cells.Find(what:=FndStrg)
        Rng.Cells.Interior.ColorIndex = 3
    Next Ws
End Sub

This code will highlight the found cell red. You didn't specify what you want done with the found cells so I had to guess at some sort of cell formatting. Let me know how it goes.

[Testify]

Hi Testify, here is a snippet of code that uses an inputbox but can be expanded into a userform. I'm not showing you a userform with this because I have no idea what your userform looks like. If you want something specific you'll have to upload a workbook. Otherwise, try adapting the following code into a userform

Sub FindOnSheets()
    Dim Ws As Worksheet
    Dim Rng As Range
    Dim FndStrg As String
    FndStrg = InputBox("Find What?", "Search")
    For Each Ws In Worksheets
        Set Rng = Ws.UsedRange.Cells.Find(what:=FndStrg)
        Rng.Cells.Interior.ColorIndex = 3
    Next Ws
End Sub

This code will highlight the found cell red. You didn't specify what you want done with the found cells so I had to guess at some sort of cell formatting. Let me know how it goes.

Great! will try this one out.. Thanks! Will give feedback in a bit.

[Testify]

Here, Attached is a copy of the file. Hope it'll give you more info about my problem. Just deleted some tabs (sheets) to lower the size down..

thanks!

[Mordred]

Hi Testify, I'm still not clear as to what you would want to do if you find the value but let me know how the workbook fares compared to your requirements. All I have the code doing is highlighting the searched value and that's it.

[Testify]

Hi Testify, I'm still not clear as to what you would want to do if you find the value but let me know how the workbook fares compared to your requirements. All I have the code doing is highlighting the searched value and that's it.

I apologize for any confusion. Please disregard the attachment. I have tried your code. Sorry I'm not that fluent with coding and userforms just trying this one out.

What I did with the code is. I have in inserted into a a button in a userform named "FIND IT". so every time i click find it your code will pop another form and will ask for a string to search. Then suppose to highlight the string but its giving me an error (Debug).

[Mordred]

Hi Testify, what is the error you are receiving?

[Testify]

Hi Testify, what is the error you are receiving?

This line: Rng.Cells.Interior.ColorIndex = 3

[Mordred]

I just ran your workbook on a different computer with my code addition and received no errors. What was your criteria regarding the search that you ran? I just used "TIERRA VERDE REALTY INC." and like I said, there were no issues.

[Testify]

I just ran your workbook on a different computer with my code addition and received no errors. What was your criteria regarding the search that you ran? I just used "TIERRA VERDE REALTY INC." and like I said, there were no issues.

I ran the workbook again. and its ok now! Thanks !

Now i just need to have it auto loaded. How can i make it like everytime i open the file it will give me the userform and it will just stay there for another search?

[Mordred]

Try the following workbook

[Testify]

Try the following workbook

Thanks a bunch!

[Mordred]

You are welcome.