Help with Exiting Sub if Value is Not Found
 

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

Help with Exiting Sub if Value is Not Found
 

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

--
Regards,
Tom Ogilvy

"Filo" wrote:

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

Help with Exiting Sub if Value is Not Found
 

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


--
Gary''s Student - gsnu200723

Help with Exiting Sub if Value is Not Found
 

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


--
Hope that helps.

Vergel Adriano


"Filo" wrote:

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

Help with Exiting Sub if Value is Not Found
 

? left("bb",1000000)
bb

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200723

Help with Exiting Sub if Value is Not Found
 

You are right Tom, I (incorrectly) assumed the problem occured only when a
was either missing or "too small"
--
Gary''s Student - gsnu200723


"Tom Ogilvy" wrote:

? left("bb",1000000)
bb

--
Regards,
Tom Ogilvy


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200723