Find using VBA

i need to do a search using VBA to activate the relevant cell

ive used

Range("A2:B2").Select
Cells.Find(What:=TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

This works fine to find the information. The problem comes if it cant find what im looking for. I would like a msg box coming up saying "Sorry the value you are searching for does not exist"

at the moment all i get is a run time error message if the text cant be found

any help would be much appreciated

Hi Thanks for the help

Tried entering the code and i get the error "invalid or unqualified reference" and it highlights .range

Hi,

Sorry for the typo .... by the way you have to adjust Range to your specific needs ...

With Range("A:A")

HTH
Carim

Originally Posted by Carim

Hi,

To test your result, you could have something along these lines

      With Range("A:A") 
            Set FoundCell = .Cells.Find(what:=Me.TextBox1.Value, _ 
                                after:=.Cells(.Cells.Count), _ 
                                LookIn:=xlFormulas, _ 
                                Lookat:=xlPart, _ 
                                searchorder:=xlByRows, _ 
                                searchdirection:=xlNext, _ 
                                MatchCase:=False)  
    End With 
    If FoundCell Is Nothing Then 
        MsgBox "Not found" 
    Else 
        MsgBox "Found it: " & FoundCell.Address 
    End If

HTH
Carim

Carim,

WHat if I'm using this one and don't find anything within the worksheet. Right now I get an error, I would like a message box to appear. Thanks

Sub sdefinder()
Dim xxx As String

For i = 0 To 100


Sheets("Master List").Select
xxx = InputBox("Enter The SDE Name Here", "SDE FINDER", xxx)

Cells.Find(what:=xxx, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Do
ireply = msgbox(Prompt:="SDE FOUND! Is " & xxx & " Correct?", _
Buttons:=vbYesNoCancel, Title:="SDE Found")
If ireply = vbNo Then
Cells.Find(what:=xxx, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ElseIf ireply = vbYes Then
Sheets("SDE Request").Select
Exit Sub
ElseIf ireply = vbCancel Then
Sheets("SDE Request").Select
Exit Sub
End If
Loop While ireply = False


If xxx = vbNullString Then
Sheets("SDE Request").Select

Exit Sub

End If






Next i


End Sub