>Does anyone know how to pass parameters to the Excel "CORREL" function to run correlations? The VBA code is this:
>
>ActiveCell.FormulaR1C1 = "=CORREL(R[-5]C:R[-2]C,R[-5]C[1]:R[-2]C[1])"
>
>but I can't decipher how the parameters are passed.
That's nearly exactly how done in VFP too :
oExcel.ActiveWorkbook.ActiveSheet.ActiveCell.FormulaR1C1 = ;
"=CORREL(R[-5]C:R[-2]C,R[-5]C[1]:R[-2]C[1])"
If you mean what does R[-5]C:R[-2]C etc mean then :
It's a relative addressing scheme. R[-5]C:R[-2]C means cells from 5 rows up in active column to 2 rows up in active column (IOW if activecell is D20 then it references to cells D15:D18). R[-5]C[1]:R[-2]C[1] means cells from 5 rows up and 1 column right to 2 rows up and 1 column left (IOW if activecell is D20 then it references to cells E15:E18).
Cetin