Subscript out of range?
 

Here is my initialization of an array:

Dim shtList As Sheets

Set shtList = Worksheets(Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page
2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page
2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page
2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page
2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page
2")) ', _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page
2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page
2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page
2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page
2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page
2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page
2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page
2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page
2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page
2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1"))

Note the commenting out after element 11. If I move that commenting element
down one row, I get a "Subscript out of range" error, whereas in the form
here I do not get the error. As I need to get all 31 elements into the array
short of doing:

Dim shtList(1 To 31) As Sheets
shtList(1) = Sheets("Earnings Balance 2003 Q4 Page 2")
shtList(2) = Sheets("Earnings Balance 2003 Q4")
....
shtList(31) = Sheets("Earnings Balance 2000")

What is it that I should be doing?

--
Dave
Temping with Staffmark
in Rock Hill, SC

Subscript out of range?
 

Maybe check you spelling. The subscript out of range error indicates that
VBA cannot find the data element you have specified for one or more of many
reasons.
Item not open, misspelled, etc. You have to figure out why it can't find it.

"Dave Birley" wrote:

Here is my initialization of an array:

Dim shtList As Sheets

Set shtList = Worksheets(Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page
2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page
2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page
2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page
2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page
2")) ', _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page
2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page
2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page
2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page
2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page
2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page
2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page
2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page
2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page
2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1"))

Note the commenting out after element 11. If I move that commenting element
down one row, I get a "Subscript out of range" error, whereas in the form
here I do not get the error. As I need to get all 31 elements into the array
short of doing:

Dim shtList(1 To 31) As Sheets
shtList(1) = Sheets("Earnings Balance 2003 Q4 Page 2")
shtList(2) = Sheets("Earnings Balance 2003 Q4")
...
shtList(31) = Sheets("Earnings Balance 2000")

What is it that I should be doing?

--
Dave
Temping with Staffmark
in Rock Hill, SC

Subscript out of range?
 

What are you trying to achieve?
This put the names of worksheets (yes?) into an array.

Option Base 1
Sub test()

Dim shtList As Variant
Dim A As Integer

Set shtList = Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page 2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page 2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page 2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page 2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page 2", _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page 2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page 2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page 2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page 2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page 2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page 2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page 2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page 2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page 2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1")

For A = 1 To 31
MsgBox ("Array Number " & A & " - " & shtList(A))
Next A


End Sub

Subscript out of range?
 

Sure helps a bunch when you have an idea what you're looking for! I got it
all excited earlier when I tried to put each one of those pups on its own row
with a continuer at the end -- too many, so I doubled them up. I was assuming
this was a similar problem -- but, with your help, I discovered that one of
the Sheets had a spare " " at the end of its name -- kablooey! Tweaked the
Sheet's name, and all is lovely in the valley!

Thanks a bunch.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"JLGWhiz" wrote:

Maybe check you spelling. The subscript out of range error indicates that
VBA cannot find the data element you have specified for one or more of many
reasons.
Item not open, misspelled, etc. You have to figure out why it can't find it.

"Dave Birley" wrote:

Here is my initialization of an array:

Dim shtList As Sheets

Set shtList = Worksheets(Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page
2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page
2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page
2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page
2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page
2")) ', _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page
2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page
2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page
2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page
2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page
2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page
2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page
2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page
2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page
2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1"))

Note the commenting out after element 11. If I move that commenting element
down one row, I get a "Subscript out of range" error, whereas in the form
here I do not get the error. As I need to get all 31 elements into the array
short of doing:

Dim shtList(1 To 31) As Sheets
shtList(1) = Sheets("Earnings Balance 2003 Q4 Page 2")
shtList(2) = Sheets("Earnings Balance 2003 Q4")
...
shtList(31) = Sheets("Earnings Balance 2000")

What is it that I should be doing?

--
Dave
Temping with Staffmark
in Rock Hill, SC

Subscript out of range?
 

Yupsies -- one of my Sheets had an errant " " at the end of its name! Thanks
for your input.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"David Sisson" wrote:

What are you trying to achieve?
This put the names of worksheets (yes?) into an array.

Option Base 1
Sub test()

Dim shtList As Variant
Dim A As Integer

Set shtList = Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page 2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page 2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page 2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page 2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page 2", _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page 2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page 2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page 2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page 2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page 2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page 2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page 2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page 2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page 2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1")

For A = 1 To 31
MsgBox ("Array Number " & A & " - " & shtList(A))
Next A


End Sub

Subscript out of range?
 

Actually the "Error" wasn't in the way I initialized the Array at all -- one
of the WS has a rogue " " at the end of its name, so for my process it was
"misspelled". <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"David Sisson" wrote:

What are you trying to achieve?
This put the names of worksheets (yes?) into an array.

Option Base 1
Sub test()

Dim shtList As Variant
Dim A As Integer

Set shtList = Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page 2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page 2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page 2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page 2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page 2", _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page 2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page 2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page 2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page 2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page 2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page 2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page 2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page 2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page 2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1")

For A = 1 To 31
MsgBox ("Array Number " & A & " - " & shtList(A))
Next A


End Sub