Myrna Larson Wrote:
Set up a separate array that contains the sources:

Dim SourceRanges() As String
ReDim SourceRanges(1 to 5)
SourceRanges(1) = .....
SourceRanges(2) = .....

Selection.Consolidate Sources:=SourceRanges(), ....

But if the code you show is what you are actually doing (i.e. just on
cell
from each of several sheets) I wouldn't use donsolidate. I would jus
retrieve
the individual values into an array, then calculate the STDEV an
AVERAGE on
the array. The worksheet functions are available from within code, i.
if the
values are in MyValues()

a = Application.Average(MyValues())
s = Application.StDev(MyValues())



On Mon, 27 Sep 2004 12:09:21 -0500, scantor145
wrote:


Visual Basic 6.0 w/Excel

I would like to use Data|Consolidate to find both the average and
standard deviation for a group of cells for several sheets.

The example below shows the code for 3 sheets, but I have a macr
that
may generate N sheets. Is there a way to generalize the code belo
to
reflect the variations in the number of sheets to consolidate?


Code:
--------------------
Selection.Consolidate Sources:=Array("[Book1]Sheet1!R1C1", _
"[Book1]Sheet2!R1C1", "[Book1]Sheet3!R1C1"), Function:=xlAverage
TopRow:=
_
False, LeftColumn:=False, CreateLinks:=False
--------------------


The range that I'm using will always be B4:G15, not just one cell. I'
not familiar with the ReDim statement. What is its purpose? Also, no
sure what goes on the right side of the SourceRanges(n) = statement.
Finally, as I mentioned in the original query, I may have 3 sheets on
time and 10 the next. At the beginning of my macro I'm prompted t
enter the number of files or sheets that need to be processed. How ca
that number be incorprated into a generalized Data|Consolidat
statement

--
scantor14
-----------------------------------------------------------------------
scantor145's Profile: http://www.excelforum.com/member.php...fo&userid=1476
View this thread: http://www.excelforum.com/showthread.php?threadid=26390