Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Consolidation
The Redim statement is the same ReDim statement you have in VB. It sets 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 to assign to that array element. That would seem to be the workbook and worksheet references. You save the number of files/sheets in a variable, then use it in the ReDim statement to set the size of the array, and presumably in the loop that 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 sheets 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. How can that number be incorprated into a generalized Data|Consolidate statement? On Tue, 28 Sep 2004 06:41:18 -0500, scantor145 wrote: 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 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 -------------------- 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 sheets 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. How can that number be incorprated into a generalized Data|Consolidate statement? |
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) |