Quote from Amos Gikunda on January 24, 2024, 5:56 am

In VBA (Visual Basic for Applications), "Run Time Error 9: Subscript out of Range" occurs when you attempt to access an array element, variable, or cell range that is outside the valid range or doesn't exist. This error can manifest in two common scenarios:

1. Array Indexing:
VBA arrays are zero-based, meaning the index of the first element is 0. If you try to access an element at an index less than 0 or greater than the array size minus 1, you will get a "Subscript out of Range" error.

Example:

Sub ArrayExample()
Dim myArray(5) As Integer ' Array with 6 elements (0 to 5)

' Attempting to access an element outside the valid range
MsgBox myArray(6) ' This will result in Run Time Error 9
End Sub

2. Sheet References and Cell Ranges:

This error can also occur when dealing with sheet references and cell ranges. Ensure that the referenced sheet exists in the workbook and that the cell or range being accessed is within the valid range of the sheet.

Example:

Sub SheetReferenceExample()
Dim ws As Worksheet
Dim myRange As Range

' Assuming there is a sheet named "Sheet1"
Set ws = Worksheets("Sheet1")

' Attempting to access a cell outside the valid range
Set myRange = ws.Range("A7") ' A7 is beyond the valid range if the sheet doesn't have enough rows

' This will result in Run Time Error 9 if A7 is outside the valid range of the sheet
MsgBox myRange.Value
End Sub

To fix this error, ensure that you are accessing valid indices within the defined range of your array or that the variables you are working with, such as sheet references and cell ranges, are properly declared, exist, and are within the valid range.

Quote from Amos Gikunda on January 24, 2024, 5:58 am

The reason why you are facing Run Time Error 9 VBA Subscript out of Range is that you have deleted "Sheet1" hence it cannot be referenced by the VBA code.