Using .Find in a Macro

[storm925]

Hello All, Here is my macro that I would like to make work more accurately...

Sub Find_Missing_Data()
On Error Resume Next
Range("A:A").Find(What:="ADD", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
MsgBox "No Missing Data Found."
End Sub

What I am having an issue with is that if there is an "ADD" in column A, the Message Box still comes up. I only want the message to activate if there really are no ADD's in column A. Just looking for some help on putting this together. Thank you, --Ben

 

[mirabeau]

Sub Find_Missing_Data()
Dim fnd
On Error Resume Next 'you probably don't need this
Set fnd = Range("A:A").Find(What:="ADD", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If fnd Is Nothing Then MsgBox "No Missing Data Found." Else fnd.Select
End Sub

 

[Trebor76]

Hi Ben,

Try this:

Sub Macro2()
    
    Dim rngFoundCell As Range
    
    Set rngFoundCell = Range("A:A").Find(What:="ADD", _
                                         LookIn:=xlValues, _
                                         LookAt:=xlWhole, _
                                         SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=True, _
                                         SearchFormat:=False)
    
    'If the 'rngFoundCell' variable has not been set, then...
    If rngFoundCell Is Nothing Then
        '...display a message informing the user so.
        MsgBox "No Missing Data Found."
    End If
    
End Sub

Or just:

Sub Macro3()

    If Evaluate("COUNTIF(A:A,""ADD"")") = 0 Then
        MsgBox "No Missing Data Found."
    End If
    

End Sub

HTH

Robert

 

[storm925]

Thank you, worked beautifully.