calculate a correlation with ranges

[Guest]

All i need is to calculate a correlation of two ranges and assign it to a
variable. I tried this but i get an 91 error (object or with block not set):

dim correlation as range
Set correlation.Formula = "=correl(inSerie" & i & ",inSerie" & j & ")"

but, if i replace "correlation" by range("a1"), then it works fine.

 

[Guest]

dim correlation as range
set correlation = Range("A1")
correlation.Formula = "=correl(inSerie" & i & ",inSerie" & j & ")"

 

[Guest]

Hi Tom! you see, I cannot do that. My database changes so I don't know where
will i have empty cells. Do you know how can I assign that correlation value
to a variable (that doesn't require a cell)?

 

[Guest]

I don't know what i and j contain or what inSerie is. These may be defined
names.

But if you look at the example in help for the correl worksheet function you
see the formula

=CORREL(A2:A6,B2:B6)

to do that in VBA

Dim correlation as double
correlation = application.Correl(Range("A2:A6"), Range("B2:B6"))


Just to demonstrate from the immediate window in the VBE:

correlation = application.Correl(Range("A2:A6"), Range("B2:B6"))
? correlation
0.997054485501581

A2:A6 and B2:B2 contain the numbers shown in the help example.

if Inserie1 is a named range and inserie2 is a named range then it would be

Dim i as Long, j as Long, correlation as double
i = 1
j = 1
correlation = application.Correl(Range("inSerie" & i), Range("inSerie" & j))

 

[Guest]

This is great!!! thanks a lot Tom!!! I've been trying for days!!!