Run-time error 9: Subscript out of range

This error is usually thrown when part of what is being referenced does not exist or is undefined.  For example the macro is referencing a worksheet that is NOT there or a workbook that is NOT open.  This error can occur when using arrays if  the macro references a non-existing array element.

Example 1:

Sheets(“Missing”).activate – will error if there is NO sheet named “Missing”.  Macro cannot find the worksheet named “Missing” so throws the error Subscript out of range.

Workbooks(“Missing.xls”).Select – will error if the workbook named “Missing” is NOT open.  Macro cannot find the referenced workbook so throws error Subscript out of range.

Example 2:

Problem: Array throws Subscript out of range error because number of array elements is unknown or a non-existent array value was referenced.

Solution: Dim or Redim the size of the array.  Use For Each Next Loop to reference the array elements instead of referencing directly.




Interested in learning new skills?