Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Operations on union of ranges
Hello all,
I use a function that returns an union of all visible cells: Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible Dim cell As Range Application.Volatile Set Vis = Nothing For Each cell In Rin If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then If Vis Is Nothing Then Set Vis = cell Else Set Vis = Union(Vis, cell) End If End If Next cell End Function The function above works well and I can use it for example to calculate the sum with '=SUM(Vis(A1:A9))'. But some functions do not work, for example '=CORREL(Vis(A1:A9),Vis (B1:B9))' give me a '#VALUE!' if the union is a union of more than one range. How can I calculate the correlation? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Operations on union of ranges
Michael,
CORREL only works on two single area ranges - your VIS may return ranges of multiple areas. If you use your code to return an array rather than a multi-area range (see code for function Vis2, below), it will work, like so: =CORREL(Vis2(A1:A9),Vis2(B1:B9)) HTH, Bernie MS Excel MVP Function Vis2(Rin As Range) As Variant 'Returns the subset of Rin that is visible Dim cell As Range Dim myV() As Variant Dim Cntr As Integer Application.Volatile ReDim myV(1 To 1) Cntr = 1 For Each cell In Rin If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then If Cntr = 1 Then myV(1) = cell.Value Cntr = Cntr + 1 Else ReDim Preserve myV(1 To Cntr) myV(Cntr) = cell.Value End If End If Next cell Vis2 = myV End Function "Mifrey" wrote in message ... Hello all, I use a function that returns an union of all visible cells: Function Vis(Rin As Range) As Range 'Returns the subset of Rin that is visible Dim cell As Range Application.Volatile Set Vis = Nothing For Each cell In Rin If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then If Vis Is Nothing Then Set Vis = cell Else Set Vis = Union(Vis, cell) End If End If Next cell End Function The function above works well and I can use it for example to calculate the sum with '=SUM(Vis(A1:A9))'. But some functions do not work, for example '=CORREL(Vis(A1:A9),Vis (B1:B9))' give me a '#VALUE!' if the union is a union of more than one range. How can I calculate the correlation? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Operations on union of ranges
Thanks it works !
Except 'Cntr = Cntr + 1' that was not well placed. Function Vis2(Rin As Range) As Variant 'Returns the subset of Rin that is visible Dim cell As Range Dim myV() As Variant Dim Cntr As Integer Application.Volatile ReDim myV(1 To 1) Cntr = 1 For Each cell In Rin If Not (cell.EntireRow.Hidden Or cell.EntireColumn.Hidden) Then If Cntr = 1 Then myV(1) = cell.Value Else ReDim Preserve myV(1 To Cntr) myV(Cntr) = cell.Value End If Cntr = Cntr + 1 End If Next cell Vis2 = myV End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set operations: union, intersection, etc. and alignment | Excel Discussion (Misc queries) | |||
Union Two With Ranges | Excel Programming | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
operations on ranges | Excel Programming | |||
VBA union of two ranges | Excel Programming |