Search across all sheets

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
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

Board Regular
Joined
Jun 25, 2002
Messages
63
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

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

Board Regular
Joined
Jun 25, 2002
Messages
63
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

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Sen_EDP,

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

Regards,

Mike
 

Forum statistics

Threads
1,144,510
Messages
5,724,774
Members
422,576
Latest member
kayth891

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 MrExcel.com.
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 "mrexcel.com".
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
Top