Dealing with errors

Lavina

Board Regular
Joined
Dec 18, 2018
Messages
75
Hello guys, ive been trying to do some error management with on error go to ErrorList.

My error list looks like this:
ErrorList:
If Err.Number = 9 Then
MsgBox ("Unable to find data, confirm if sheet: " + populatingArray(i, 4) + " is open")
End If

Error 9 is: Subscript out of range

So every time this error pops up, id like to inform the user that the sheet is not open, and to give him a choice to either: close the macro, open the sheet that was missing and retry running.

Since there's a long process that comes beforehand i don't want to restart the whole sub and id like the code to return to the initial position before the error was encountered and try to run again with the correct sheet open, how do i do that?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,083
Office Version
  1. 2019
Platform
  1. Windows
You can use resume Next in your error handling routine

something like following may do what you want

Code:
On Error GoTo ErrorList






ErrorList:
    If Err.Number = 9 Then
       If MsgBox("Unable to find data, confirm if sheet: " & populatingArray(i, 4) & " is open" & _
       Chr(10) & "Do You Want To Continue?", 36, "Error") = vbYes Then
        Err.Clear
        Resume Next
        End If
    Else
        MsgBox (Error(Err)), 48, "Error"
    End If
End Sub

You can read more about error handling here:https://excelmacromastery.com/vba-error-handling/

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,591
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top