Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exiting Excel | Excel Discussion (Misc queries) | |||
Exiting a Textbox | Excel Programming | |||
Exiting Worksheet | Excel Discussion (Misc queries) | |||
Exiting a workbook | Excel Programming | |||
Exiting a workbook | Excel Programming |