Hi All,

I'm trying to create a macro to calculate a correlation matrix of data series.

I have a worksheet called "All" with 17 columns of data, each column is a data series of numbers.

I would like to create a 17 x 17 correlation matrix in another sheet for the multiple data series using the Pearson formula.

Depending on a user input "TimePeriod" (a number from 1-7), the number of rows of data in "All", used for the calculation, varies.

So far I have:

My problem stems from the use of syntax for the range in the Pearson parameters, and entering a letter to represent the column during the loops. Can I use a numerical reference to the columns or can I loop through the Column letters?

Could any of you guys possibly help?

Thanks very much

Steve

I'm trying to create a macro to calculate a correlation matrix of data series.

I have a worksheet called "All" with 17 columns of data, each column is a data series of numbers.

I would like to create a 17 x 17 correlation matrix in another sheet for the multiple data series using the Pearson formula.

Depending on a user input "TimePeriod" (a number from 1-7), the number of rows of data in "All", used for the calculation, varies.

So far I have:

Code:

```
Dim TimePeriod, rowcount, colcount, EndRow As Integer
Dim myrange1, myrange2 as Range
TimePeriod = Worksheets("Corr").Cells(9, "B").Value
End_Row = IIf(TimePeriod = 1, 13, IIf(TimePeriod = 2, 25, IIf(TimePeriod = 3, 69, IIf(TimePeriod = 4, 137, _
IIf(TimePeriod = 5, 247, IIf(TimePeriod = 6, 507, 1005))))))
' I would now like to cycle through the 17x17 cell range and calculate the correlation values in a macro, I guess using a couple of "for" statements.
With Range("E27:G29")
For rowcount = 1 to 17
For colcount = 1 to 17
myrange1 = [rowcount][1]:[rowcount][EndRow] ' incorrect code but I require help (at least) here
myrange2 = [colcount][1]:[colcount][EndRow] ' and here
.Value = Application.WorksheetFunction.Pearson(myrange1, MyRange2)
Next colcount
Next rowcount
End With
End Sub
```

Could any of you guys possibly help?

Thanks very much

Steve

Last edited: