Runtime Error 9 in Excel VBA: “Subscript out of range,” explained and fixed

Error 9 — “Subscript out of range” — means the code asked for something by name or by number and that thing is not there: a worksheet with a slightly different name, an array index past the end, a workbook that isn't open. VBA is rarely wrong about this. Something the code believes exists, doesn't.

What it means

Every name or index used to pull an item out of a collection — Worksheets("Report"), arr(7), Workbooks("Data.xlsx") — is a “subscript.” When no item matches, VBA raises error 9 rather than returning nothing. Ninety percent of cases are a name that doesn't match exactly, or a count that isn't what the code assumed.

Find which lookup failed

Click Debug when the error appears; the failing line highlights.
Look at the name or index inside the brackets on that line. Hover variables to see their values — a sheet name built from a cell is wrong surprisingly often.
Compare character by character with the real thing: trailing spaces in sheet names ("Report ") are invisible and fatal.

The usual causes, most common first

The worksheet name doesn't match — renamed sheet, typo, extra space, or the sheet is in a different workbook than the code assumes.
The workbook isn't open — Workbooks("name.xlsx") only works for open workbooks, and the name must include the extension exactly as shown in the title bar.
An array index is out of bounds — looping one step too far, or an array that came back smaller than expected (an empty range produces a one-item or zero-item array).
The sheet exists but is very hidden or the code runs against the wrong active workbook.
A Split() or filter result has fewer parts than expected — the delimiter wasn't in the text.

The fix

Print the real names. In the Immediate window (Ctrl+G): loop For Each ws In Worksheets: Debug.Print "[" & ws.Name & "]": Next — brackets expose hidden spaces.
Refer to sheets by codename for sheets inside the macro's own workbook (the name in parentheses in the VBA editor's Project window). Codenames survive users renaming tabs.

Check before you grab, in code:

Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Report")
On Error GoTo 0
If ws Is Nothing Then MsgBox "Sheet 'Report' not found": Exit Sub

For arrays, respect the bounds: loop For i = LBound(arr) To UBound(arr) instead of assuming a size, especially with arrays built from ranges or Split().

Do not download anything for this error. Nothing is broken in Excel itself. A name or an index in the macro doesn't match reality, and only comparing the two fixes it.

If it still happens

The mismatch is dynamic: the name is being built while the macro runs — from a cell, a date, a file listing. Print the built name with Debug.Print right before the failing line and compare it against what actually exists at that moment. Nine times out of ten, the difference is a space, a date format, or a renamed tab.

Common questions

Does error 9 mean the file is corrupted? No. It means the macro couldn't find something it referenced. The workbook itself is fine.

Why did the macro work yesterday and fail today? Something it references changed: a sheet was renamed, a file was saved under a new name or not opened, or the data shrank (fewer rows means a smaller array than the loop expects).