Cycle Through Worksheets - not cycling

[JSnow]

I have a workbook w/ many sheets which I need to occassionaly cycle through
to find a policy number. I have written code to exclude sheet "Sheet1" and
search all other sheets. It does not work.

Here is the code:

Sub FindPolicy()

Dim ws As Worksheet
Dim policy As String

policy = Application.InputBox(prompt:="Enter policy number to find", _
Title:="FIND POLICY", Type:=1)

If policy = "False" Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
On Error Resume Next

Set pFind = Cells.Find(What:=policy, after:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)

On Error GoTo 0

If pFind Is Nothing Then
lReply = MsgBox("Policy cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then
Run "FindPolicy"
Else
End
End If
Else
pFind.Select
End If
End If
Next

End Sub

 

[Sam Wilson]

You're cycling through sheets but referring to cells on whatever sheet you
happened to be on. Change:

Set pFind = Cells.Find(

to

Set pFind = ws.Cells.Find(

 

[Bernie Deitrick]

Change

Set pFind = Cells.Find(What...

to

Set pFind = ws.Cells.Find(What....


and change

pFind.Select

to

ws.Select
pFind.Select


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ooops.. one more:

after:=Range("A1"), _

needs to be

after:=ws.Range("A1"), _


HTH,
Bernie
MS Excel MVP

 

[Jacob Skaria]

Try the below...Modified a bit

Sub FindPolicy()

Dim ws As Worksheet
Dim policy As String

policy = Application.InputBox(prompt:="Enter policy number to find", _
Title:="FIND POLICY", Type:=1)
If policy = "False" Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
On Error Resume Next

Set pFind = ws.Cells.Find(What:=policy, after:=ws.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)

If Not pFind Is Nothing Then
ws.Activate
pFind.Select
Exit Sub
End If

End If
Next

lReply = MsgBox("Policy cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then
Run "FindPolicy"
Else
End
End If

End Sub

If this post helps click Yes