Stumped on Array
 

Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move some
sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Befo=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am getting a
"Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto

Stumped on Array
 

Works fine for me Otto.

Just the old chestnut, MISSING references in the ToolsReferences in the
VBIDE.

--
HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move
some
sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Befo=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am getting
a
"Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto

Stumped on Array
 

The code works for me too, Otto. I'd be curious what ExemptList(3)
evaluates to when you hit the error and go into debug. The only way I can
understand the error is if the array populated by the second sub is a
different array than the public one. That can't be with the code you posted
but I wonder if the real code it different in some way.

--
Jim
"Otto Moehrbach" wrote in message
...
| Excel 2002, WinXP
| I've shortened the code to show my problem. The object here is to move
some
| sheets to the far left. The code is:
| Option Explicit
| Public ExemptList() As String
|
| Sub Macro1
| Call BuildExemptArray
| Call MoveExemptSheets
| End Sub
|
| Sub BuildExemptArray()
| ReDim ExemptList(1 To 3)
| ExemptList(1) = "AddrList"
| ExemptList(2) = "ChkList"
| ExemptList(3) = "ClientShtsList"
| End Sub
|
| Sub MoveExemptSheets()
| Dim c As Long
| For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
| line
| Sheets(ExemptList(c)).Move Befo=Sheets(1)
| Next
| End Sub
|
| This code is straightforward and I've used it before, but now I am getting
a
| "Subscript out of range'" error on the "For c =" line.
| I can see I might get this error with the subsequent line if one of the
| array sheets didn't exist or is hidden, but I can't see a reason for this
| error with this line.
| Thanks for your help. Otto
|
|

Stumped on Array
 

The code worked for me.
(Are you *sure* the correct workbook is active? but, to your point, if this
were the case it wouldn't be this line causing the error)

Otherwise, the only thing I can tell you is to try the things you do when
things go inexplicably buggy: reboot & try again. If still an problem,
delete the offending line and retype it (hey, it works for me...sometimes)

....or maybe run Rob Bovey's Code Cleaner.
http://www.appspro.com/Utilities/Utilities.htm

Good luck,
--
George Nicholson

Remove 'Junk' from return address.


"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move
some sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Befo=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am getting
a "Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto

Stumped on Array
 

"George Nicholson" wrote in message
...

. If still an problem,
delete the offending line and retype it (hey, it works for me...sometimes)

Too often for our sanity :-).

Stumped on Array
 

Too often for our sanity :-).

oh, any claim I may have had to sanity expired circa Excel 4 :-)

--
George Nicholson

Remove 'Junk' from return address.


"Bob Phillips" wrote in message
...

"George Nicholson" wrote in message
...

. If still an problem,
delete the offending line and retype it (hey, it works for
me...sometimes)

Too often for our sanity :-).

Stumped on Array
 

Thanks to all. I'll beat on it some more in the morning. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move
some sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Befo=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am getting
a "Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto

Stumped on Array
 

To all
I'm embarrassed to say this, but my real code had the calls for the two
macros switched, so that the code was trying to use the array before it
built the array. Up until now I've always been proud that I was almost
sane. Now that's gone.
Thanks for your help and for taking the time. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move
some sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Befo=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am getting
a "Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto

Stumped on Array
 

Sometimes you just have to settle for next-day sane. At least you still
have that.

:-)

--
George Nicholson

Remove 'Junk' from return address.


"Otto Moehrbach" wrote in message
...
To all
I'm embarrassed to say this, but my real code had the calls for the two
macros switched, so that the code was trying to use the array before it
built the array. Up until now I've always been proud that I was almost
sane. Now that's gone.
Thanks for your help and for taking the time. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I've shortened the code to show my problem. The object here is to move
some sheets to the far left. The code is:
Option Explicit
Public ExemptList() As String

Sub Macro1
Call BuildExemptArray
Call MoveExemptSheets
End Sub

Sub BuildExemptArray()
ReDim ExemptList(1 To 3)
ExemptList(1) = "AddrList"
ExemptList(2) = "ChkList"
ExemptList(3) = "ClientShtsList"
End Sub

Sub MoveExemptSheets()
Dim c As Long
For c = UBound(ExemptList) To LBound(ExemptList) Step -1 '**Problem
line
Sheets(ExemptList(c)).Move Befo=Sheets(1)
Next
End Sub

This code is straightforward and I've used it before, but now I am
getting a "Subscript out of range'" error on the "For c =" line.
I can see I might get this error with the subsequent line if one of the
array sheets didn't exist or is hidden, but I can't see a reason for this
error with this line.
Thanks for your help. Otto