Data Consolidation
 

Visual Basic 6.0 w/Excel

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

The example below shows the code for 3 sheets, but I have a macro tha
may generate N sheets. Is there a way to generalize the code below t
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:=Fals
-------------------

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

Data Consolidation
 

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 one cell
from each of several sheets) I wouldn't use donsolidate. I would just retrieve
the individual values into an array, then calculate the STDEV and AVERAGE on
the array. The worksheet functions are available from within code, i.e 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 macro that
may generate N sheets. Is there a way to generalize the code below 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
--------------------