Search across all sheets


Board Regular
Jun 25, 2002
Hello Forum,

I am using code found on this forum to do a search across all worksheets. This works great for the first instace of, but if I continue to search I get a "Object variable or With block variable not set" error. If I type in something that I know isn't in the workbook, it goes through most of the sheets and then I get "Unable to get the Find property of the Range class".

The code I am using is:
What = InputBox("Search for :")
If What = "" Then Exit Sub
For Each sht In Worksheets
Set Found = sht.Cells.Find(What)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Response = MsgBox("Continue?", vbYesNo + vbQuestion)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
End If
Next sht
MsgBox "Search Ended!"

I have yet to see the "Search Ended!" message pop up as I cannot get that far.

Any help would be greatly appreciated!


Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Thanks for answering Mike.

Like yours, I have tried to implement many code samples posted on this forum - always with some sort of error (yours for instance stops at "Set foundCell = .Cells.Find( _
What:=strSearchString, _
LookIn:=xlValues, _
LookAt:=xlPart)" and gives the "unable to get the find property of the Range class" error message (but if I enter something I know is not in the workbook, I will get the appropriate "XXXXX not found" message).

The one I posted in my original message is the closest I've gotten - it will find the first instance of, but will fail after that.

Any ideas?

This message was edited by yippie_ky_yay on 2002-09-19 05:02
Upvote 0

I just tried the above code again and it worked - but now fails again. Before, I would notice that it would cycle through most of the sheets before failing.

Is it possible that my machine is too fast for Excel 97 (Windows XP, P4 1.8GHz)? Or that there are simply too many sheets in my workbook (about 50)?
Upvote 0
I have sent you an email with an attached workbook containing my code (it does work).

It is doubtful whether the speed of your machine has anything to do with the macro crashing (although I am not familiar with XP). The 50 worksheets should not trouble the macro.

Incidentally, your code works perfectly on my machine. I did however add the following line.

"Dim sht As Worksheet" (without the apostrophes)

I have also sent you a workbook with your code.


Upvote 0
Hey Mike,

thank you so much for really going all out to help me here!

Both workbooks you sent me work perfectly.

Originally I wrote this code that searched all Sheetnames. If none were found, it was then that the code was supposed to search in all sheets. I must've done something wrong with the integration of the two (in an "IF - ELSE" statement) because they do work seperately now. I should be ok from here!

Thanks again Mike!

Upvote 0

for searching through all sheets
right-click on a sheet tab ,click select all sheets, then goto main menu, edit, find

it works , without vba.

Upvote 0

That method works in Excel 2000, but not in Excel 97.


Upvote 0

Forum statistics

Latest member
Dave Carr QM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back