View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
JSnow JSnow is offline
external usenet poster
 
Posts: 54
Default Find sub not finding

Hey guys. I have a bit of code that's supposed to match an inputted number
and see if it exists anywhere else in the workbook (except on "Sheet1"). It
doesn't work. Here's the code:

Sub FindPolicy()

Dim ws As Worksheet

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)

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