Error Handling

[Shay A]

Hi,
I want to insert a msgbox to inform the user that no errors found in the formulas in the selection.
How do I combine the ON ERROR in the following code?

Sub data_to_last_row_month()

lr = Cells(Rows.Count, 19).End(xlUp).Row
msg = MsgBox("NO ERRORS")

Application.ScreenUpdating = False

Range("a2:r2").Select
Selection.AutoFill Destination:=Range("A2:R" & lr)
Range("A2:R" & lr).Select
Selection.SpecialCells(xlCellTypeFormulas, 16).Select

Application.ScreenUpdating = True

End Sub


TY!

 

[PCosta87]

Hi,

The "On error" statement is used to pass instructions for when the code runs into an error and not for errors in the sheet itself.
In any case, from your description you need the message box to appear if no errors are found... if there are no errors in the code then "On error" won't be triggered, which leads me to believe this probably isn't what you are after.

I'm sorry but I can't make sense of the request... could you please elaborate?
Thanks!

 

[Shay A]

Hi,
So maybe to capture the 1004 runtime error? If the error doesn't occur so put the msgbox?

 

[PCosta87]

Hi,
So maybe to capture the 1004 runtime error? If the error doesn't occur so put the msgbox?

If the error doesn't occur it means the code completed all the instructions without breaking... In this case, simply add the message box at the end of the code and it will be displayed.

 

[Debaser]

Here's one way:

Sub data_to_last_row_month()
    Dim rgErrors As Range
    Dim lr As Long
    lr = Cells(Rows.Count, 19).End(xlUp).Row
   
    Application.ScreenUpdating = False
   
    Range("a2:r2").AutoFill Destination:=Range("A2:R" & lr)
    On Error Resume Next
    Set rgErrors = Range("A2:R" & lr).SpecialCells(xlCellTypeFormulas, 16)
    On Error GoTo 0
    If rgErrors Is Nothing Then MsgBox ("NO ERRORS")
   
    Application.ScreenUpdating = True

End Sub

 

[Shay A]

hello,
I have found this useful
If Err.Number = 1004 Then
MsgBox "NO ERRORS"
Exit Sub
End If

TY