Searching for a value in a workbook

[AZGJC]

I'm trying to write some code that will find a value in a workbook. I'm using cells.find code, but it only works when I'm on the appropriate sheet before I run the code. Any help would be appreciated.

Thanks,
Garrett

 

[PHV]

A starting point:
For Each oSH In ActiveWorkbook.Worksheets
oSH.Cells.Find ...
...
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AZGJC]

oSH? Sorry, I'm learning on my own.

 

[PHV]

oSH is simply a variable:
Dim oSH As Worksheet
For ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AZGJC]

That's what I thought, thanks.

 

[AZGJC]

Still not working, perhaps if I show you the code. I'm getting a value for uResponse (and it's only in one place in the entire workbook depending on what day it is). Again, this works if the sheet that the value is in is selected before I run it.

Workbooks("Resource Planner Report.xls").Activate
Dim wksht As Worksheet
For Each wksht In ActiveWorkbook.Worksheets
wksht.Cells.Find(What:=uResponse, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True).Activate

 

[PHV]

And what about something like this ?
Dim wksht As Worksheet, c As Range
For Each wksht In ActiveWorkbook.Worksheets
Set c = wksht.Cells.Find(What:=uResponse, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True)
If Not c Is Nothing Then
wksht.Activate
c.Select
Exit For
End If
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AZGJC]

That's it, thanks for the help!

 

[xlbo]

If you are using Excel 2003, there is no need to search each worksheet - there is an option to search the whole workbook


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question