• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 6
  • 6
  • 2
2 Solutions
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
I meant MsgBox of course.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
You need an Exit Sub right before the Err_NotFound: line.
0
 
Seamus2626Author Commented:
Yep, just worked it out! Thanks
0
 
Martin LissRetired ProgrammerCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now