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))
--
Regards,
Tom Ogilvy
"Paco" wrote:
> 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)?
>
> "Tom Ogilvy" wrote:
>
> > dim correlation as range
> > set correlation = Range("A1")
> > correlation.Formula = "=correl(inSerie" & i & ",inSerie" & j & ")"
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Paco" wrote:
> >
> > > 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.
> > >
|
Similar Threads
