Cycle Through Worksheets - not cycling

  • Thread starter Thread starter JSnow
  • Start date Start date
J

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
 
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(
 
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
 
Ooops.. one more:

after:=Range("A1"), _

needs to be

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


HTH,
Bernie
MS Excel MVP
 
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
 
Back
Top