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)).EntireRo
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
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)).EntireRo
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Martin Liss
I meant MsgBox of course.
ASKER
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("MasterG
On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRo
On Error GoTo 0
Exit Sub
Err_NotFound:
Windows("currentmonth.xlsx
Windows("MappingTable.xlsM
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
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("MasterG
On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRo
On Error GoTo 0
Exit Sub
Err_NotFound:
Windows("currentmonth.xlsx
Windows("MappingTable.xlsM
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
Add this right before the Application.Quit line
ThisWorkbook.Saved = True
ThisWorkbook.Saved = True
ASKER
But i dont want the work book to save, so
ThisWorkbook.Saved = False ?
ThisWorkbook.Saved = False ?
Setting it to True "fools" Excel into thinking it's been saved already.
No, saved = true does not save, just tells excel it has been.
ASKER
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
Columns("A:A").Select
Selection.Find(What:="Mast
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
On Error GoTo Err_NotFound
Set Found = Range("A:A").Find("MasterG
On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRo
On Error GoTo 0
Err_NotFound:
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Windows("MappingTable.xlsm
Application.DisplayAlerts = True
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
DeleteBlankRows
End Sub
Full sub
Sub Clean()
Cells.Select
With Selection
.WrapText = False
.MergeCells = False
End With
If ActiveSheet.AutoFilterMode
Columns("A:A").Select
Selection.Find(What:="Mast
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
On Error GoTo Err_NotFound
Set Found = Range("A:A").Find("MasterG
On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRo
On Error GoTo 0
Err_NotFound:
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Windows("MappingTable.xlsm
Application.DisplayAlerts = True
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
DeleteBlankRows
End Sub
ASKER
Problem solved!
Thanks guys
Err_NotFound:
Application.DisplayAlerts = False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Windows("MappingTable.xlsm
Application.DisplayAlerts = True
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
Thanks guys
Err_NotFound:
Application.DisplayAlerts = False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Windows("MappingTable.xlsm
Application.DisplayAlerts = True
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
ASKER
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
Columns("A:A").Select
Selection.Find(What:="Mast
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
Set Found = Range("A:A").Find("MasterG
On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRo
On Error GoTo 0
Err_NotFound:
Application.DisplayAlerts = False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Windows("MappingTable.xlsm
Application.DisplayAlerts = True
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
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
Columns("A:A").Select
Selection.Find(What:="Mast
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activ
Set Found = Range("A:A").Find("MasterG
On Error GoTo Err_NotFound
Range(Range("A1"), Found.Offset(-1)).EntireRo
On Error GoTo 0
Err_NotFound:
Application.DisplayAlerts = False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close savechanges:=False
Windows("MappingTable.xlsm
Application.DisplayAlerts = True
MsgBox "New monthly file not saved over currentmonth.xls"
Windows("Controls.xlsm").C
Application.Quit
You need an Exit Sub right before the Err_NotFound: line.
ASKER
Yep, just worked it out! Thanks
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.