Search always ends in a find, but should not


C

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....
 
Ad

Advertisements

M

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
 
Ad

Advertisements

J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top