Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data consolidation | Excel Worksheet Functions | |||
Data Consolidation | Excel Discussion (Misc queries) | |||
data consolidation | Excel Discussion (Misc queries) | |||
Need how-to or example for data consolidation | Excel Worksheet Functions | |||
data consolidation | Excel Discussion (Misc queries) |