Array Problem
 

I'm currently using an array to cycle through a set of 4 worksheets,
so that I can perform similar operations to all of them. From my
understanding, I set up the array correctly, and it works well, but it
seems to deconstruct itself in the middle of my process.

Through my own testing, I've found that the array works fine when I
first start referencing it, but during a particular For...Next Loop,
Array(x) is replaced with Empty during it's respective call in the
loop. Here's what I'm using:

Dim A As Variant
A = Array(wksht3, wksht4, wksht5, wksht6) '<-- wksht3-6 are variables
I created

'First call (intSystems is another variable set to 26 in this
instance)
For Counter = 0 To 3
With A(Counter).Range("A2:A" & intSystems)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Next Counter

'Second Call
For Counter = 0 To 3
With A(Counter)
.Range("B2").Formula = "='Market Direction'!A2"
.Range("B2:B" & intSystems).FillDown
.Range("I2").Formula = "='Market Direction'!B2"
.Range("I2").Copy Destination:=.Range("I2:K" & intSystems)
.Range("AG2").Formula = "='Market Direction'!E2"
.Range("AG2:AG" & intSystems).FillDown
End With
Next Counter

I've figured out that my problem only occurs when I use a With block
with the array, but no modifiers. "With A(Counter)" will always set
everything to empty, but "With A(Counter).Range("A1")" will work fine.
It doesn't seem to matter what commands are called inside the block.

Does anybody have any ideas/suggestions? Also, is there a better way
for me to do operations on 4 worksheets at once?

Any help would be appreciated to get me going again.
-bgetson

Array Problem
 

Change

With A(Counter).Range("A2:A" & intSystems)

to

With Worksheets(A(Counter)).Range("A2:A" & intSystems)




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


wrote in message
oups.com...
I'm currently using an array to cycle through a set of 4 worksheets,
so that I can perform similar operations to all of them. From my
understanding, I set up the array correctly, and it works well, but it
seems to deconstruct itself in the middle of my process.

Through my own testing, I've found that the array works fine when I
first start referencing it, but during a particular For...Next Loop,
Array(x) is replaced with Empty during it's respective call in the
loop. Here's what I'm using:

Dim A As Variant
A = Array(wksht3, wksht4, wksht5, wksht6) '<-- wksht3-6 are variables
I created

'First call (intSystems is another variable set to 26 in this
instance)
For Counter = 0 To 3
With A(Counter).Range("A2:A" & intSystems)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Next Counter

'Second Call
For Counter = 0 To 3
With A(Counter)
.Range("B2").Formula = "='Market Direction'!A2"
.Range("B2:B" & intSystems).FillDown
.Range("I2").Formula = "='Market Direction'!B2"
.Range("I2").Copy Destination:=.Range("I2:K" & intSystems)
.Range("AG2").Formula = "='Market Direction'!E2"
.Range("AG2:AG" & intSystems).FillDown
End With
Next Counter

I've figured out that my problem only occurs when I use a With block
with the array, but no modifiers. "With A(Counter)" will always set
everything to empty, but "With A(Counter).Range("A1")" will work fine.
It doesn't seem to matter what commands are called inside the block.

Does anybody have any ideas/suggestions? Also, is there a better way
for me to do operations on 4 worksheets at once?

Any help would be appreciated to get me going again.
-bgetson

Array Problem
 

I think the trouble is that your are making an array of objects (worksheets)
then alter those objects and then
refer to the old objects before they were changed.
How about something like this:

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
'do your stuff here
Next


RBS


wrote in message
oups.com...
I'm currently using an array to cycle through a set of 4 worksheets,
so that I can perform similar operations to all of them. From my
understanding, I set up the array correctly, and it works well, but it
seems to deconstruct itself in the middle of my process.

Through my own testing, I've found that the array works fine when I
first start referencing it, but during a particular For...Next Loop,
Array(x) is replaced with Empty during it's respective call in the
loop. Here's what I'm using:

Dim A As Variant
A = Array(wksht3, wksht4, wksht5, wksht6) '<-- wksht3-6 are variables
I created

'First call (intSystems is another variable set to 26 in this
instance)
For Counter = 0 To 3
With A(Counter).Range("A2:A" & intSystems)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Next Counter

'Second Call
For Counter = 0 To 3
With A(Counter)
.Range("B2").Formula = "='Market Direction'!A2"
.Range("B2:B" & intSystems).FillDown
.Range("I2").Formula = "='Market Direction'!B2"
.Range("I2").Copy Destination:=.Range("I2:K" & intSystems)
.Range("AG2").Formula = "='Market Direction'!E2"
.Range("AG2:AG" & intSystems).FillDown
End With
Next Counter

I've figured out that my problem only occurs when I use a With block
with the array, but no modifiers. "With A(Counter)" will always set
everything to empty, but "With A(Counter).Range("A1")" will work fine.
It doesn't seem to matter what commands are called inside the block.

Does anybody have any ideas/suggestions? Also, is there a better way
for me to do operations on 4 worksheets at once?

Any help would be appreciated to get me going again.
-bgetson

Array Problem
 

Thanks for your quick input. I guess I made the assumption that the
array would update its reference to the worksheet after changing it -
I agree (and so does Microsoft Help) that was probably my major
mistake.

"For Each....Next" makes my problem seem a whole lot easier than I
tried to make it.

Thanks again for the help.