VBA GoTo Statement

VBA GoTo statement one can use when an error occurs while running the code rather than displaying an error to resume the next line of code by ignoring the error message. There are two kinds of GOTO statements: select any worksheet range in the specified workbook and error handler.

To overcome the anticipated errors in VBA, we have a function called “GOTO.” We will see both kinds of GoTo statements in this article.

#1 - Application.GoTo Method

We can use the statement Application if you want to go to the specific workbook or worksheet in all the opened Workbooks.GoTo method.

Let us look at the syntax of the Application.GoTo method.

• : This is nothing but a specific cell reference. If the reference is not there by default, it will take you to the last used cell range.
• : This is a logical statement of TRUE or FALSE. If the value is TRUE, it will scroll through the window. If the value is FALSE, it will not scroll through the window.

Example

We can use the Goto method if you want to go to a specific cell in the worksheet. For example, we have 3 sheets: Jan, Feb, and Mar.

If we want to go to cell C5 in the Jan sheet, we will use the below set of codes.

Step 1: Start the excel macro name.

Code:

Sub GoTo_Example1()

End Sub

Step 2: Start the method “Application.GoTo“

Code:

Sub GoTo_Example1()

Application.Goto

End Sub

Step 3: We need to specify the worksheet name in the reference argument. Then, in that worksheet, we need to mention the specific cell.

Code:

Sub GoTo_Example1()

Application.Goto Reference:=Worksheets("Jan").Range("C5")

End Sub

Step 4: Mention the scroll as TRUE.

Code:

Sub GoTo_Example1()

Application.Goto Reference:=Worksheets("Jan").Range("C5"),Scroll:=True

End Sub

Step 5: Now, run this code using the F5 key, or you can also run it manually. It will take you to the specified sheet and specified cell.

Now, we will change the scroll argument to FALSE and see the change it will encounter.

Sub GoTo_Example1()

Application.Goto Reference:=Worksheets("Jan").Range("C5"), Scroll:=False

End Sub

If you want to go to a specific workbook, you need to mention the workbook name before the workbook name.

Sub GoTo_Example1()

Application.Goto Reference:=Workbooks("Book1.xlsx").Worksheets("Jan").Range("C5"), Scroll:=False

End Sub

#2 - Error Handler Method

When a particular line of code encounters an error, VBA stops executing the rest of the code and shows the error message.

For example, look at the below line of code.

Sub GoTo_Example2()

  Sheets("April").Delete

  Sheets.Add

End Sub

The above code says to delete the sheet in April and add a new sheet. In the active workbook, if there is any sheet named April, it will delete or show the below error message dialog box.

When we ran this code, the workbook did not have a sheet called April, so the VBA code could not find the sheet name and threw the error. Sometimes, we need to ignore this error because often, if there is no sheet name called April, then we need to go on with the other lines of code.

We can use the GoTo method as an error handler to eliminate this error.

We will modify the code as the "On Error" goes to the next line.

Sub GoTo_Example2()

On Error GoTo NextLine
Sheets("April").Delete

NextLine:
Sheets.Add

End Sub

If we run this, it will add the new sheet even though there is no sheet named April.

The statement "On Error GoTo NextLine" understands that if any error occurs, it will jump to the next line, and in the next line, the VBA code is to add a new sheet.

• We can also use the On Error Resume Next VBA statement if you want to jump to the next line when the error occurs.
• To jump to the next, we must be sure that that particular line of code expected must be an error.
• If the important line of the code skips with this error handler, we may not finish your task perfectly.