fboehlandt
10-01-2008, 05:29 AM
Hi everyone,I would like to calculate the variance of a product array of two ranges. The Range().Formulaarray is not an option, since I just want to save the result as a variable to be used in follow-up calculations. I have come up with the code below that contains the evaluate method. Unfortunately, it produces non-sensical results (a very high number) and I haven't been able to figure out what this is calculating infact:
Sub Testfile() Dim wb As Workbook Dim testsheet_roi, testsheet_aum, result As Worksheet Dim rng_roi, rng_aum As Range Dim Variance As Double Set wb = ThisWorkbook Set roi = wb.Worksheets("testsheet_roi") Set aum = wb.Worksheets("testsheet_aum")'Here I set the two relevant ranges: Set rng_roi = roi.Range("B2:K2") Set rng_aum = aum.Range("B2:K2")'I would like to calculate the variance of the product array of the two ranges: Variance = Evaluate("Var(" & rng_roi.Address & "*" & rng_aum.Address & ")") MsgBox (Variance)End SubCan anyone help please? Thanx
Sub Testfile() Dim wb As Workbook Dim testsheet_roi, testsheet_aum, result As Worksheet Dim rng_roi, rng_aum As Range Dim Variance As Double Set wb = ThisWorkbook Set roi = wb.Worksheets("testsheet_roi") Set aum = wb.Worksheets("testsheet_aum")'Here I set the two relevant ranges: Set rng_roi = roi.Range("B2:K2") Set rng_aum = aum.Range("B2:K2")'I would like to calculate the variance of the product array of the two ranges: Variance = Evaluate("Var(" & rng_roi.Address & "*" & rng_aum.Address & ")") MsgBox (Variance)End SubCan anyone help please? Thanx