Error message & Close spreadsheet

Hi,

I have some code that finds a word in column A and deletes any rows above it, if users have not saved down a fresh file, then there will be no rows above that word and you get an application defined or object defined error 1004 as there are no rows to delete, the word will be in row1 !

So it errors here

      Range(Range("A1"), Found.Offset(-1)).EntireRow.Delete

Can i add an If statement above this line saying if is that error 1004 comes up, a message box appears saying "New monthly file not saved over currentmonth.xls" and then the code quits the current workbook controls.xlsm

Many thanks
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If Found Is Nothing Then
    Magbox  "New monthly file not saved over currentmonth.xls" 
    Application.Quit
End If

Open in new window

0
SteveCommented:
I would use error handling to sort the issue:

''''' FIRST CODE '''''

On Error goto Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRow.Delete
On Error goto 0

''''' MORE CODE '''''

Exit Sub
Err_NotFound:
    Msgbox  "New monthly file not saved over currentmonth.xls" 
    Application.Quit
End Sub

Open in new window


This will handle all errors in that small area.

@Martin: Whats a MagBox? Are there tissues near by?  :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I meant MsgBox of course.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Seamus2626Author Commented:
Guys,

I have went with the below code, but annoyingly im getting a message box asking me do i want to save changes to currentmonth.xls

i thought by adding , false that excel realises i dont want to save changes

Thanks

       
    Set Found = Range("A:A").Find("MasterGroup Name")
   

On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRow.Delete
On Error GoTo 0



Exit Sub
Err_NotFound:

    Windows("currentmonth.xlsx").Close , False
    Windows("MappingTable.xlsM").Close , False
    MsgBox "New monthly file not saved over currentmonth.xls"
    Windows("Controls.xlsm").Close , False
    Application.Quit
0
Martin LissOlder than dirtCommented:
Add this right before the Application.Quit line

ThisWorkbook.Saved = True
0
Seamus2626Author Commented:
But i dont want the work book to save, so

ThisWorkbook.Saved = False ?
0
Martin LissOlder than dirtCommented:
Setting it to True "fools" Excel into thinking it's been saved already.
0
SteveCommented:
No, saved = true does not save, just tells excel it has been.
0
Seamus2626Author Commented:
Lads, im nearly cracking up here, that workbook currentmonth will not close without the inbuilt message box, im trying everything!

Full sub

Sub Clean()

 
   
    Cells.Select
    With Selection
        .WrapText = False
        .MergeCells = False
    End With

    If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter

    Columns("A:A").Select
    Selection.Find(What:="Mastergroup Name", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
       
   
   On Error GoTo Err_NotFound
   
    Set Found = Range("A:A").Find("MasterGroup Name")
   

    On Error GoTo Err_NotFound
    Range(Range("A1"), Found.Offset(-1)).EntireRow.Delete
    On Error GoTo 0



 
Err_NotFound:

    Application.DisplayAlerts = False
    ThisWorkbook.Saved = True
    ActiveWorkbook.Close savechanges:=False
    Windows("MappingTable.xlsm").Close , False
    Application.DisplayAlerts = True
    MsgBox "New monthly file not saved over currentmonth.xls"
    Windows("Controls.xlsm").Close , False
    Application.Quit

     
 
   
    DeleteBlankRows

End Sub
0
Seamus2626Author Commented:
Problem solved!

Thanks guys

Err_NotFound:

    Application.DisplayAlerts = False
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close savechanges:=False
    Windows("MappingTable.xlsm").Close , False
    Application.DisplayAlerts = True
    MsgBox "New monthly file not saved over currentmonth.xls"
    Windows("Controls.xlsm").Close , False
    Application.Quit
0
Seamus2626Author Commented:
Small problem here guys,

the sub is always going to Err_Not Found:

So is not leaving this sub!

 On Error GoTo Err_NotFound
 
   
    Cells.Select
    With Selection
        .WrapText = False
        .MergeCells = False
    End With

    If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter

    Columns("A:A").Select
    Selection.Find(What:="Mastergroup Name", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
       
   

   
    Set Found = Range("A:A").Find("MasterGroup Name")
   

    On Error GoTo Err_NotFound
    Range(Range("A1"), Found.Offset(-1)).EntireRow.Delete
    On Error GoTo 0



 
Err_NotFound:

    Application.DisplayAlerts = False
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close savechanges:=False
    Windows("MappingTable.xlsm").Close , False
    Application.DisplayAlerts = True
    MsgBox "New monthly file not saved over currentmonth.xls"
    Windows("Controls.xlsm").Close , False
    Application.Quit
0
Martin LissOlder than dirtCommented:
You need an Exit Sub right before the Err_NotFound: line.
0
Seamus2626Author Commented:
Yep, just worked it out! Thanks
0
Martin LissOlder than dirtCommented:
BTW it would help us if you wrap your code in [ Code] tags which is done by selecting the code and then clicking the "Code" button above the new post window.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.