Look for text in column

[bradyj7]

Hi want the code to search for 'Journey Start Event' in column E and if it cannot find it then to exit the macro. I thought the code below would do it, but it doesn't seem to work. Any suggestions? Either to the code below or different code?

thanks

Dim myCell1 As Range
LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).row
 Set myCell1 = Range("E" & LastRow2)
    Set found = Columns("E").Find(What:="Journey Start Event", LookIn:=xlValues, LookAt:=xlPart)
        If found Is Nothing Then Exit Sub

 

[MrKowz]

Try the following - you didn't have "found" dimmed.

Dim myCell1 As Range, _
    found   As Range
LastRow2 = Range("E" & Rows.Count).End(xlUp).Row
Set myCell1 = Range("E" & LastRow2)
Set found = Columns("E").Find(What:="Journey Start Event", LookIn:=xlValues, LookAt:=xlPart)
    If found Is Nothing Then Exit Sub

 

[JackDanIce]

This works fine for me when the text is in column E and when it is not:

Sub MyDestroyedTurtleOfDoom()
Dim LastRow2 As Integer
Dim myCell1 As Range, found As Range
LastRow2 = ActiveCell.SpecialCells(xlCellTypeLastCell).row
 Set myCell1 = Range("E" & LastRow2)
    Set found = Columns("E").Find(What:="Journey Start Event", LookIn:=xlValues, LookAt:=xlPart)
        If found Is Nothing Then
            Exit Sub
        Else
            MsgBox found.Address
        End If
End Sub

 

[bradyj7]

Hi thanks,

That code is working fine. I would like to make one small change though.

I would like the save the file to a folder called bad data and then exit the routine. This is the code to save it

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="C:\Bad data\" & ActiveWorkbook.Name

How do I change the if then formula to do this?

Thank you