Data Consolidation

[scantor145]

The Redim statement is the same ReDim statement you have in VB. It set
the
dimensions of a dynamic array, as I showed in my code example.

What goes on the right side of the equal sign is whatever you want t
assign
to that array element. That would seem to be the workbook an
worksheet
references.

You save the number of files/sheets in a variable, then use it in th
ReDim
statement to set the size of the array, and presumably in the loop tha
loads
the values of the array elements.

Dim Rsp as String, N as Integer, S As Integer
Dim Template As String, SourceRanges() As String

Rsp = Inputbox("Enter number of sheets")
If Rsp = "" Then Exit Sub
If IsNumeric(Rsp) = False Then
'error message and exit
Else
N = CInt(Rsp)
End If

Template = "[Book1]Sheet#!B4:G15"
Redim SourceRanges(1 To N)
For S = 1 To N
SourceRanges(S) = Replace(Template,"#", Format$(S))
Next S
Selection.Consolidate Sources:=SourceRanges(), _
TopRow:= False, LeftColumn:=False, CreateLinks:=False

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

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. jus one
cell
from each of several sheets) I wouldn't use donsolidate. I woul 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


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 cod 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
--------------------

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

Thanks for the response. I actually did figure out what I needed t
do. See below:

At the beginning of my macro, I'm prompted for the number of files
NumFiles, to process. This is equivalent to the number of sheets that
will be produced. Data|Consolidate occurs on a new worksheet that is
added during the process. Below are the key statements that allows me
to consolidate data (average and standard deviation) for N sheets,
where N = NumFiles:



Code:
--------------------
Dim SourceRanges() As String
ReDim SourceRanges(1 To NumFiles)

For I = 1 To NumFiles
SourceRanges(I) = "'" & Sheets(I).Name & "'!R4C2:R15C7"
Next I

Selection.Consolidate Sources:=SourceRanges(), Function:=xlAverage, TopRow:=False, _
LeftColumn:=False, CreateLinks:=Falseor