Search always ends in a find, but should not

[Corey]

Can anyone see why the below code results in a MasgBox "Found", even though
there is NO Sheet2.Range("A1").value in Sheet3(SavedData) ?

Sub SaveCoverPage()
' Check to see if the Record Already Exists
Dim rngFound As Range
On Error Resume Next
' Gain the Location of the Cover Page Title
With Worksheets("SavedData").Range("A:A")

Set rngFound = .Find(What:=Sheet2.Range("A1").Value, After:=Cells(1),
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _

SearchDirection:=xlNext,
MatchCase:=True, Matchbyte:=True)
If rngFound.Value <> "" Then ' <==== If there is a Sheet2Range("A1").value
found
MsgBox "Found" '
<----------------------------------------------------------- I ALWAYS get
this result, even if there is NO value in the SavedData sheet matching the
valuer in Sheet 2 A1.
Else ' <===== =============== If there is Not Sheet2.Range("A1").value
found
MsgBox "Not Found"
End If
End With
End Sub



Corey....

 

[marcus]

Hi Corey

Try this for size. This should sort your problem out.

Take care

Marcus

Option Explicit
Sub SaveCoverPage()

Dim rngFound As Range
Dim MyVar As String
On Error Resume Next
MyVar = Sheets(2).Range("A1").Value

With Worksheets("SavedData").Range("A:A")
Set rngFound = Cells.Find(MyVar, LookIn:=xlValues)
If Not rngFound Is Nothing Then
MsgBox rngFound
Else
MsgBox "Not Found"
End If
End With

End Sub

 

[Corey]

Thanks Marcus

 

[JLGWhiz]

Sometimes VBA distinguishes between Empty, zero and empty string, so to be
on the safe side, instead of If <> "" it might be better to use something
like If > "" . That way if the cell has a positive value of any kind, it
will register found, but if it does not have a positive value, it will
register, not found.