Search across all sheets

[yippie_ky_yay]

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
sht.Activate
Set Found = sht.Cells.Find(What)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Activate
Response = MsgBox("Continue?", vbYesNo + vbQuestion)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
Loop
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!

-Sean

 

[Ekim]

Sean,

For an alternative macro, see my contribution to the thread at:

http://www.mrexcel.com/board/viewtopic.php?topic=22176&forum=2

Regards,

Mike

 

[yippie_ky_yay]

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?

Thanks,
-Sean
This message was edited by yippie_ky_yay on 2002-09-19 05:02

 

[yippie_ky_yay]

Hello,

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)?

 

[Ekim]

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.


Regards,


Mike

 

[yippie_ky_yay]

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!

-Sean

 

[sen_edp]

hello

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.

hth

 

[Ekim]

Sen_EDP,

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

Regards,

Mike