Help writing If statement

[bradyj7]

Hi all,

The code below (part of a larger macro) searches for the text 'Journey End Event' in column E and deletes all the rows after it. However sometimes, the text 'Journey End Event' is not present and as expected and error occurs. My question is, how can I modify it so that If 'Journey End Event' is not found then Exit Sub?

All help appreciated

John

Sub journey()
..............
'Delete all rows after Journey end event
LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set myCell1 = Range("E" & LastRow2)
    Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        Set myCell = ActiveCell(2, 0)
        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete
...............
End Sub

 

[JamesW]

Untested, as I can't see the rest of your code.

Try something like this, I can't guarentee it will work but may help you work it out:

lRow = Range("E1").End(xlDown).Row
Set sFound = Range("E1:E" & lRow).Find("Journey End Event")
If Not sFound Is Nothing Then Range(myCell, sFound).EntireRow.Delete
If sFound Is Nothing Then Exit Sub

 

[pbornemeier]

Sub journey()
'..............
'Delete all rows after Journey end event
Dim oFound As Object
Dim LastRow2 As Long
Dim myCell1 As Range
Dim myRange As Range
Dim myCell As Range
    LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    Set myCell1 = Range("E" & LastRow2)
    Set oFound = Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)
    If Not oFound Is Nothing Then
        Set myCell = oFound(2, 0)
        Set myRange = Range(oFound, myCell1)
        myRange.EntireRow.Delete
    End If
'...............
End Sub

 

[zizzerboy]

Another way of doing it (bit more messy than JamesW):

Sub journey()
..............
'Delete all rows after Journey end event
 
on error goto ErrorOut
 
LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set myCell1 = Range("E" & LastRow2)
    Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        Set myCell = ActiveCell(2, 0)
        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete
on error goto 0
goto PastError
 
ErrorOut:
msgbox "'Journey End Event' could not be found"
exit sub
 
PastError:
................
 
End Sub

 

[bradyj7]

Thanks all - got it to work.

John