Cycle through sheets

[Djblois]

I want to cycle through all the sheets in an openworkbook to see if there is any with a certain condition. Here is the code I tried. Where am I going wrong?

[vba]Sub test()
Dim detailtest As Worksheet
Set ActiveWorkbook = wB(1)
For detailtest = Worksheet(1) To Worksheet(2)
On Error Resume Next
Set findString = Range("C1", "D1").Find(What:="Whse")
If findString Is Nothing Then

Else
MsgBox "it works."
Next detailtest
End Sub
[/vba]

[Djblois]

I have it closer now. Here is my new code:

[vba]
For Each detailtest In wB(1).Sheets
On Error Resume Next
Set findString = Range("C1", "D1").Find(What:="Whse")
If findString Is Nothing Then
MsgBox "not this one"
Else
Set detail = wB(1).ActiveSheet

MsgBox "it works."
End If
Next
[/vba]

If the sheet meets the condition I need it to activate the sheet

[johnske]

Try... [vba]
Option Explicit

Sub test2()
'for two sheets
Dim N As Long, Findstring As Range

For N = 1 To 2
Set Findstring = Worksheets(N).Range("C1", "D1").Find(What:="Whse")
If Not Findstring Is Nothing Then MsgBox "it works."
Next

End Sub

Sub TestAll()
'for all sheets
Dim detailtest As Worksheet, Findstring As Range

For Each detailtest In Worksheets
Set Findstring = detailtest.Range("C1", "D1").Find(What:="Whse")
If Not Findstring Is Nothing Then MsgBox "it works."
Next

End Sub
[/vba]EDIT: In the 1st procedure I took your use of 'Worksheet(1) To Worksheet(2)' to mean you only wanted to look at two worksheets. I you want to look at every worksheet use
For N = 1 To Worksheets.Count instead of For N = 1 To 2

[Djblois]

John thank you mine is working where with the msgbox but how would I select the sheet that meets the condition and then end the loop

[johnske]

E.G.[VBA]
Sub TestAll()

'for all sheets
Dim detailtest As Worksheet, Findstring As Range

For Each detailtest In Worksheets
Set Findstring = detailtest.Range("C1", "D1").Find(What:="Whse")
If Not Findstring Is Nothing Then
detailtest.Activate
Findstring.Select
Exit For
End If
Next

End Sub
[/VBA]

[Djblois]

It is only working if I have the tab with Whse in it activated. Not if another one of the tabs are selected.

[Djblois]

I stepped through the code and it isn't going to the next sheet

[johnske]

??? not having that problem on my machine??? Try this variation... [VBA]
Sub TestAll()
'for all sheets
Dim detailtest As Worksheet, Findstring As Range

For Each detailtest In Worksheets
detailtest.Activate
Set Findstring = detailtest.Range("C1", "D1").Find(What:="Whse")
If Not Findstring Is Nothing Then
Findstring.Select
Exit For
End If
Next

End Sub
[/VBA]

[Djblois]

This is what I have and it is not working:

[VBA]For Each detailTest In Worksheets
On Error Resume Next
Set Findstring = Range("C1", "D1").Find(What:="Whse")
If Not Findstring Is Nothing Then
Findstring.Select
Set detail = wB(1).ActiveSheet
MsgBox "it works."
Exit For
End If
Next[/VBA]

[johnske]

This is what I have and it is not working:

[vba]For Each detailTest In Worksheets
On Error Resume Next
Set Findstring = Range("C1", "D1").Find(What:="Whse")
If Not Findstring Is Nothing Then
Findstring.Select
Set detail = wB(1).ActiveSheet
MsgBox "it works."
Exit For
End If
Next[/vba]

This line... [VBA]Set Findstring = Range("C1", "D1").Find(What:="Whse")[/VBA]because you have not specified a worksheet the default is 'ActiveSheet', and hence only the active sheet is being searched. You must specify, use [VBA]Set Findstring = detailtest.Range("C1", "D1").Find(What:="Whse")[/VBA]and what does this mean? [VBA]Set detail = wB(1).ActiveSheet[/VBA]where are detail and wB declared?