Find using VBA

[yorkshirewhite]

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

[Carim]

Hi,

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

Please Login or Register  to view this content.

HTH
Carim

[yorkshirewhite]

Hi Thanks for the help

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

[Carim]

Hi,

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

Please Login or Register  to view this content.

HTH
Carim

[julvaz]

Hi,

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

Please Login or Register  to view this content.

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