Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union over multiple sheets
Is it possible to get a Union of ranges over multiple sheets? Something like:
Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union over multiple sheets
In the Excel object model sheets are the containers for range objects. This
means that a range object can not span more than one sheet (they are contained within the sheet). The only work around is to deal with the range objects seperately. What exactly are you trying to do? -- HTH... Jim Thomlinson "Randall" wrote: Is it possible to get a Union of ranges over multiple sheets? Something like: Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union over multiple sheets
I have data on multiple sheets for which I want to calculate median and
standard deviation. I am trying to use the worksheet functions (Median and Stdev) to do the calculation. The only work around I know of is to read in all my values and then write my own median and standard deviation functions. Any better ideas? Randall "Jim Thomlinson" wrote: In the Excel object model sheets are the containers for range objects. This means that a range object can not span more than one sheet (they are contained within the sheet). The only work around is to deal with the range objects seperately. What exactly are you trying to do? -- HTH... Jim Thomlinson "Randall" wrote: Is it possible to get a Union of ranges over multiple sheets? Something like: Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union over multiple sheets
The STDEV and MEDIAN worksheetfunctions can take multiple ranges from different sheets:
Set rngA = Sheets(1).Range("A1:A5") Set rngb = Sheets(2).Range("B2:B10") MsgBox "Standard Deviation is " & Application.StDev(rngA, rngb) MsgBox "Median is " & Application.Median(rngA, rngb) will do what you want. HTH, Bernie MS Excel MVP "Randall" wrote in message ... I have data on multiple sheets for which I want to calculate median and standard deviation. I am trying to use the worksheet functions (Median and Stdev) to do the calculation. The only work around I know of is to read in all my values and then write my own median and standard deviation functions. Any better ideas? Randall "Jim Thomlinson" wrote: In the Excel object model sheets are the containers for range objects. This means that a range object can not span more than one sheet (they are contained within the sheet). The only work around is to deal with the range objects seperately. What exactly are you trying to do? -- HTH... Jim Thomlinson "Randall" wrote: Is it possible to get a Union of ranges over multiple sheets? Something like: Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union over multiple sheets
Nope, that would seem to be the best way to do it.
-- HTH RP (remove nothere from the email address if mailing direct) "Randall" wrote in message ... I have data on multiple sheets for which I want to calculate median and standard deviation. I am trying to use the worksheet functions (Median and Stdev) to do the calculation. The only work around I know of is to read in all my values and then write my own median and standard deviation functions. Any better ideas? Randall "Jim Thomlinson" wrote: In the Excel object model sheets are the containers for range objects. This means that a range object can not span more than one sheet (they are contained within the sheet). The only work around is to deal with the range objects seperately. What exactly are you trying to do? -- HTH... Jim Thomlinson "Randall" wrote: Is it possible to get a Union of ranges over multiple sheets? Something like: Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FYI
Thank you for the help.
Just FYI, I found another work around by putting strings together for the formula. MyString = "=MEDIAN(" For i = 1 to N C = ... MyString = MyString & "'" & Sheet(i).Name & "'!C" & C Next i MyString =Left(MyString,Len(MyString)-1) & ")" 'Remove Last , Add a ) Range("A1").FormulaR1C1 = MyString "Bernie Deitrick" wrote: Randall, That many sheets is a problem using the technique that I showed. But not a problem overall. I would simply use a temporary sheet: Sub CalcMedianAndStDev() Dim N As Integer Dim i As Integer Dim mySht As Worksheet N = Worksheets.Count Set mySht = Worksheets.Add mySht.Move After:=Worksheets(N + 1) For i = 1 To N 'N is the number of Sheet containing data Sheets(i).Range("A1:A5").Copy _ mySht.Range("A65536").End(xlUp)(2) Next i MsgBox Application.Median(mySht.Range("A65536").End(xlUp) .CurrentRegion) MsgBox Application.StDev(mySht.Range("A65536").End(xlUp). CurrentRegion) Application.DisplayAlerts = False mySht.Delete Application.DisplayAlerts = True End Sub HTH, Bernie MS Excel MVP "Randall" wrote in message ... Yes, that answers the question as I posted it. Let me see if you can help with a follow up question. The number of sheet containing data is variable (anywhere from 1 to 50 sheets). My plan was to Union all the ranges together: For i = 1 to N 'N is the number of Sheet containing data If i = 1 Then AllRange = Sheets(i).Range("A1:A5") Else Rng = Sheets(i).Range("A1:A5") AllRange = Union(AllRange,Rng) End If Next i 'The Resulting AllRange is for N sheets X = Application.WorksheetFunction.Median(AllRange) The problem using .Median(RngA, RngB, etc.) is that for a give case I will not know how many Ranges (Sheets) make up the collection. I suppose I could have a huge set of if statements from 1 to 50. Any better ideas? Thanks for your answer to my previous question and any future help you may provide. Randall "Bernie Deitrick" wrote: The STDEV and MEDIAN worksheetfunctions can take multiple ranges from different sheets: Set rngA = Sheets(1).Range("A1:A5") Set rngb = Sheets(2).Range("B2:B10") MsgBox "Standard Deviation is " & Application.StDev(rngA, rngb) MsgBox "Median is " & Application.Median(rngA, rngb) will do what you want. HTH, Bernie MS Excel MVP "Randall" wrote in message ... I have data on multiple sheets for which I want to calculate median and standard deviation. I am trying to use the worksheet functions (Median and Stdev) to do the calculation. The only work around I know of is to read in all my values and then write my own median and standard deviation functions. Any better ideas? Randall "Jim Thomlinson" wrote: In the Excel object model sheets are the containers for range objects. This means that a range object can not span more than one sheet (they are contained within the sheet). The only work around is to deal with the range objects seperately. What exactly are you trying to do? -- HTH... Jim Thomlinson "Randall" wrote: Is it possible to get a Union of ranges over multiple sheets? Something like: Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
FYI
Randall,
Just FYI, that will blow up when you add up too many ranges - the limit is either based on characters or ranges, depending on your exact approach, which is why I did not use that technique for your 50 sheets. HTH, Bernie MS Excel MVP "Randall" wrote in message ... Thank you for the help. Just FYI, I found another work around by putting strings together for the formula. MyString = "=MEDIAN(" For i = 1 to N C = ... MyString = MyString & "'" & Sheet(i).Name & "'!C" & C Next i MyString =Left(MyString,Len(MyString)-1) & ")" 'Remove Last , Add a ) Range("A1").FormulaR1C1 = MyString "Bernie Deitrick" wrote: Randall, That many sheets is a problem using the technique that I showed. But not a problem overall. I would simply use a temporary sheet: Sub CalcMedianAndStDev() Dim N As Integer Dim i As Integer Dim mySht As Worksheet N = Worksheets.Count Set mySht = Worksheets.Add mySht.Move After:=Worksheets(N + 1) For i = 1 To N 'N is the number of Sheet containing data Sheets(i).Range("A1:A5").Copy _ mySht.Range("A65536").End(xlUp)(2) Next i MsgBox Application.Median(mySht.Range("A65536").End(xlUp) .CurrentRegion) MsgBox Application.StDev(mySht.Range("A65536").End(xlUp). CurrentRegion) Application.DisplayAlerts = False mySht.Delete Application.DisplayAlerts = True End Sub HTH, Bernie MS Excel MVP "Randall" wrote in message ... Yes, that answers the question as I posted it. Let me see if you can help with a follow up question. The number of sheet containing data is variable (anywhere from 1 to 50 sheets). My plan was to Union all the ranges together: For i = 1 to N 'N is the number of Sheet containing data If i = 1 Then AllRange = Sheets(i).Range("A1:A5") Else Rng = Sheets(i).Range("A1:A5") AllRange = Union(AllRange,Rng) End If Next i 'The Resulting AllRange is for N sheets X = Application.WorksheetFunction.Median(AllRange) The problem using .Median(RngA, RngB, etc.) is that for a give case I will not know how many Ranges (Sheets) make up the collection. I suppose I could have a huge set of if statements from 1 to 50. Any better ideas? Thanks for your answer to my previous question and any future help you may provide. Randall "Bernie Deitrick" wrote: The STDEV and MEDIAN worksheetfunctions can take multiple ranges from different sheets: Set rngA = Sheets(1).Range("A1:A5") Set rngb = Sheets(2).Range("B2:B10") MsgBox "Standard Deviation is " & Application.StDev(rngA, rngb) MsgBox "Median is " & Application.Median(rngA, rngb) will do what you want. HTH, Bernie MS Excel MVP "Randall" wrote in message ... I have data on multiple sheets for which I want to calculate median and standard deviation. I am trying to use the worksheet functions (Median and Stdev) to do the calculation. The only work around I know of is to read in all my values and then write my own median and standard deviation functions. Any better ideas? Randall "Jim Thomlinson" wrote: In the Excel object model sheets are the containers for range objects. This means that a range object can not span more than one sheet (they are contained within the sheet). The only work around is to deal with the range objects seperately. What exactly are you trying to do? -- HTH... Jim Thomlinson "Randall" wrote: Is it possible to get a Union of ranges over multiple sheets? Something like: Set RngA = Sheets(1).Range("A1:A5") Set RngB = Sheets(2).Range("B2:B10") Set Rng = Union(RngA, RngB) The above code give a "Method 'Union' of object '_Global' failed" Error, ie You can't Union ranges from multiple sheets. Does anyone know a workaround to this? Tx, Randall |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
Union or Join Sheets | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
Union Method with multiple worksheets | Excel Programming | |||
Changing the value in multiple sheets without selecting those sheets | Excel Programming |