help with If statement

[bradyj7]

Hi there,

The code below which is part of a larger macro looks for the text 'Journey End Event' in column E and deletes all rows below it. However, sometimes this text in not present and an error occurs. My question is how can I modify it so that if 'Journey End Event' is not found then exit the journey function. The journey function is called inside another function, so if the text is not present then I want it to Exit sub.

Thank you

Journey()
Dim LastRow2 As Long, myCell As Range, myRange As Range
Dim myCell1 As Range

***********Other code************

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

*************other code***************

End sub

 

[VoG]

Try

Set found = Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)
If found Is Nothing Then Exit Sub
found.Select

 

[bradyj7]

Hi,

Do you mean do this

Journey()
Dim LastRow2 As Long, myCell As Range, myRange As Range
Dim myCell1 As Range
Dim found 

LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    Set myCell1 = Range("E" & LastRow2)
    Set found = 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
        
    If found Is Nothing Then Exit Sub
    found.Select

End sub

 

[VoG]

No, I meant like this

Dim LastRow2 As Long, myCell As Range, myRange As Range
Dim myCell1 As Range
Dim found

LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    Set myCell1 = Range("E" & LastRow2)
    Set found = Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)
        If found Is Nothing Then Exit Sub
        found.Select

        Set myCell = ActiveCell(2, 0)

        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete
        
End Sub

 

[bradyj7]

Hi,

I get a 'Type mismatch' error on this line

Set found = Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)

Thank you

 

[VoG]

Sory, try

Dim LastRow2 As Long, myCell As Range, myRange As Range
Dim myCell1 As Range
Dim found As Range

LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    Set myCell1 = Range("E" & LastRow2)
    Set found = Cells.Find(What:="Journey End Event", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)
        If found Is Nothing Then Exit Sub
        found.Select

        Set myCell = ActiveCell(2, 0)

        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete
        
End Sub

 

[bradyj7]

Hi,

Nope that causes an 'object required' error on the same line?

Thank you

 

[VoG]

Maybe

Dim LastRow2 As Long, myCell As Range, myRange As Range
Dim myCell1 As Range
Dim found As Range

LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    Set myCell1 = Range("E" & LastRow2)
    Set found = myCell1.Find(What:="Journey End Event", LookIn:=xlValues, LookAt:=xlPart)
        If found Is Nothing Then Exit Sub
        found.Select

        Set myCell = ActiveCell(2, 0)

        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete
        
End Sub

 

[bradyj7]

Hi,

Nope that is not working either. No error occurs but it is exiting the routine even though 'journey end event' is present in the column.

Thank you

 

[VoG]

Try

Set found = Columns("E").Find(What:="Journey End Event", LookIn:=xlValues, LookAt:=xlPart)