Help with Exiting Sub if Value is Not Found

[Filo]

Hello-
I would like the code below to not go in debug mode if the variable a is not
found. What is the code I should insert? Thank you!

Public Sub Macro1()

Dim nRow As Integer, i As Integer, a As String

If Not Intersect(activecell, Range("A1:E77")) Is Nothing Then

a = Left(activecell.Offset(0, 5).Text, 9)

Sheets("MTD-DTB").Select
Range("A1").Select

Cells.Find(What:=a, After:=activecell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

nRow = activecell.Row

For i = nRow To 1000

If Range("B" & i).Value = a Then

Range("A" & nRow, "G" & i).Select

End If

Next i

End sub

 

[Tom Ogilvy]

Public Sub Macro1()

Dim nRow As Integer, i As Integer, a As String
Dim r As Range
If Not Intersect(ActiveCell, Range("A1:E77")) Is Nothing Then

a = Left(ActiveCell.Offset(0, 5).Text, 9)

Sheets("MTD-DTB").Select
Range("A1").Select

Set r = Cells.Find(What:=a, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not r Is Nothing Then
nRow = r.Row

For i = nRow To 1000

If Range("B" & i).Value = a Then

Range("A" & nRow, "G" & i).Select

End If

Next i
Else
MsgBox a & " not found"
End If
End If
End Sub

 

[Gary''s Student]

v = ActiveCell.Offset(0, 5).Text
If Len(v) < 9 Then
Exit Sub
End If
a = Left(ActiveCell.Offset(0, 5).Text, 9)

 

[Vergel Adriano]

Filo,

I assume the problem you're having is when the Find function doesn't find a
match. I'm not sure I follow what you're trying to accomplish with the For
loop after the Find, but maybe the code snippet below would give you the idea
on how to handle cases when Find function does not find a match.

Dim rFound As Range
Set rFound = Cells.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)

If Not rFound Is Nothing Then
'match was found
nRow = rFound.Row
For i = nRow To 1000
If Range("B" & i).Value = a Then
Range("A" & nRow, "G" & i).Select
End If
Next i
End If

 

[Tom Ogilvy]

? left("bb",1000000)
bb

 

[Gary''s Student]

You are right Tom, I (incorrectly) assumed the problem occured only when a
was either missing or "too small"