fboehlandt
10-06-2008, 09:45 AM
Hi everyone,
I am trying to calculate the joint variance of two (discontinuous) ranges. Let's assume I have data in column A through C and I would like to calculate the variance of Column A and C only. This is what I have so far:
Sub Test()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng1, rng2 As Range
Dim Variance As Double
Set wb = ThisWorkbook
Set ws = wb.Worksheets("sheet1")
Set rng1 = ws.Range("A1:A1000")
Set rng2 = ws.Range("C1:C1000")
Set rng_union = Union(rng1, rng2)
Variance = Evaluate("Var('" & rng_union.Parent.Name & "'!" & rng_union.Address & ")")
MsgBox (Variance)
End Sub
which returns a 'type mismatch' error. I think I'm on the wrong track using the Union method but I have no idea how to go about this. Any ideas? Thanks in advance...
I am trying to calculate the joint variance of two (discontinuous) ranges. Let's assume I have data in column A through C and I would like to calculate the variance of Column A and C only. This is what I have so far:
Sub Test()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng1, rng2 As Range
Dim Variance As Double
Set wb = ThisWorkbook
Set ws = wb.Worksheets("sheet1")
Set rng1 = ws.Range("A1:A1000")
Set rng2 = ws.Range("C1:C1000")
Set rng_union = Union(rng1, rng2)
Variance = Evaluate("Var('" & rng_union.Parent.Name & "'!" & rng_union.Address & ")")
MsgBox (Variance)
End Sub
which returns a 'type mismatch' error. I think I'm on the wrong track using the Union method but I have no idea how to go about this. Any ideas? Thanks in advance...