In the Excel VBA development, encountering errors can be a common yet frustrating experience. A frequent issue that specially new developers encounter is the "Select Method of Range Class Failed" error. This article aims to unveil this error, offering insights into its cause and presenting a solution to resolve it.
Understanding the "Select Method of Range Class Failed" Error
Cause of the Error
How to Fix the Error
Understanding the "Select Method of Range Class Failed" Error
The "Select Method of Range Class Failed" error typically arises when VBA code attempts to select a range that is inaccessible at the time of the code execution.
Cause of the "Select Method of Range Class Failed" Error
Understanding the underlying cause is critical to resolving the error. Below is the reason for this error.
Inactive Worksheet
Trying to select a range on a worksheet that is not currently active can lead to this error. Let’s consider this example Excel workbook.
This Excel file contains two worksheets. “Input” and “Output” sheets. Currently we are in the “Input” sheet. So the “Input” sheet is the worksheet active at the moment. Assume we tried to select the A1 cell of the “Output” now using the below subroutine.
Dim WS_Input As Worksheet
Dim WS_Output As Worksheet
Set WS_Input = Worksheets("Input")
Set WS_Output = Worksheets("Output")
WS_Output.Range("A1").Select
End Sub
Here what we are doing is we are asking the macro to select a cell from a worksheet which is not active. So this will throw the following error - Run-time error '1004' - Select method of Range class failed.
How to Fix the Error
To tackle this error, we can simply activate the required sheet. So before selecting a range, make sure the relevant sheet is activated using Worksheet.Activate method. Here is how you can modify the above subroutine to do that.
Dim WS_Input As Worksheet
Dim WS_Output As Worksheet
Set WS_Input = Worksheets("Input")
Set WS_Output = Worksheets("Output")
WS_Output.Activate
WS_Output.Range("A1").Select
End Sub
