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.

 

 

 

[efb_likebox fanpage_url="https://www.facebook.com/vbamarket/" box_width="840" box_height=340"" responsive="1" show_faces="1" show_stream="1" hide_cover="1" small_header="0" hide_cta="0" locale="en_US"]