PDA

View Full Version : Cycle through sheets



Djblois
09-14-2006, 05:59 PM
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?

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

Djblois
09-14-2006, 06:13 PM
I have it closer now. Here is my new code:


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


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

johnske
09-14-2006, 06:29 PM
Try...
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
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
09-14-2006, 06:53 PM
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
09-14-2006, 07:05 PM
E.G.
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

Djblois
09-14-2006, 07:13 PM
It is only working if I have the tab with Whse in it activated. Not if another one of the tabs are selected.

Djblois
09-14-2006, 07:19 PM
I stepped through the code and it isn't going to the next sheet

johnske
09-14-2006, 07:23 PM
??? not having that problem on my machine??? Try this variation...
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

Djblois
09-14-2006, 07:33 PM
This is what I have and it is not working:

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

johnske
09-14-2006, 07:44 PM
This is what I have and it is not working:

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
NextThis line... Set Findstring = Range("C1", "D1").Find(What:="Whse")because you have not specified a worksheet the default is 'ActiveSheet', and hence only the active sheet is being searched. You must specify, use Set Findstring = detailtest.Range("C1", "D1").Find(What:="Whse")and what does this mean? Set detail = wB(1).ActiveSheetwhere are detail and wB declared?