Excel VBA GoTo Statement

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

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA GoTo Statement (wallstreetmojo.com)

2 Ways to use GoTo Statement in VBA Code

#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.

  • [Reference]: This is nothing but a specific cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more. If the reference is not there by default, it will take you to the last used cell range.[Scroll]: 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.

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 macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more name.

Code:

Sub GoTo_Example1()

End Sub

Step 2: Start the method “Application.GoTo“

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.

Sub GoTo_Example1()

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

End Sub

Step 4: Mention the scroll as TRUE.

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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.

Things to Remember

  • We can also use the On Error Resume Next VBAOn Error Resume Next VBAVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error.read more 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.

This article has been a guide to VBA GoTo. Here we learn how to use the VBA GoTo statement using the Application.GoTo and Error Handler Method along with examples and downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • MsgBox in Excel VBAExcel VBA On Error Goto 0Excel VBA GoTo StatementVBA Selection