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

As a Guess it is one of these parameters.

LookIn:=xlFormulas, LookAt:=xlWhole,
You could just try
LookIn:=xlValues, LookAt:=xlPart,

If that does not work what happens when you do a manual find. If that works
record it and take a look at the code generated...
--
HTH...

Jim Thomlinson


"JSnow" wrote:

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