# Macro for correlation matrix with varying data length

#### Stebaker

##### New Member
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:

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]:[rowcount][EndRow] ' incorrect code but I require help (at least) here
myrange2 = [colcount]:[colcount][EndRow]   ' and here
.Value = Application.WorksheetFunction.Pearson(myrange1, MyRange2)
Next colcount
Next rowcount
End With
End Sub``````
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

Last edited:

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### shg

##### MrExcel MVP
On what sheet does the data appear, starting in what cell?

Do the 13, 25, 69 ... values mark the number of rows of data or the last row of data?

On what sheet should the output appear, starting in what cell?

You want to calculate the correlation of each column of the data with each other column?

#### AlphaFrog

##### MrExcel MVP
This is an example of the syntax you can use to define a range of cells using integers. I don't follow exactly what range you want to define.
Code:
``            myrange1 = Range(Cells(rowcount, 1), Cells(rowcount, colcount))``

#### shg

##### MrExcel MVP
Code:
`` myrange1 = Range(Cells(rowcount, 1), Cells(rowcount, colcount))``

In the code, myrange1 is a Variant and myrange2 is a Range. That line would return the values of the first range to the Variant (which would be fine), but the next line would give a run-time error.

#### Stebaker

##### New Member

Hi,

Thanks for quick responses....

shg4421

• data should appear E27:G29 on my active sheet (called "Corr")
• data series appears in sheet called "All", range B2:Rx
• x = EndRow defined by user (13, 25,...)
• Yes, a standard correlation matrix with 1's on the top left to bottom right diagonal
AlphaFrog

Thanks for the line of code, I have tried this below, but there is an error with

Code:
`` myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))``
Code:
``````Sub CorrMatrix()
'
'

'

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 Worksheets("Corr").Range("E27:G29")

For rowcount = 1 To 17
For colcount = 1 To 17

myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))
myrange2 = Range(Worksheets("All").Cells(1, colcount), Worksheets("All").Cells(EndRow, colcount))
.Value = Application.WorksheetFunction.Pearson(myrange1, myrange2)

Next colcount
Next rowcount

End With

End Sub``````
Cheers Guys!

Steve

#### shg

##### MrExcel MVP
Try this:
Code:
``````Sub x()
Dim iPer        As Long
Dim lRow        As Long
Dim rInp        As Range
Dim iCol1       As Long
Dim iCol2       As Long

iPer = Worksheets("Corr").Range("B9").Value
If iPer < 1 Or iPer > 7 Then Exit Sub

lRow = Choose(iPer, 13, 25, 69, 137, 247, 507, 1005)
With Worksheets("All")
Set rInp = .Range("B2", .Cells(lRow, "R"))
End With

With Worksheets("Corr").Range("E27")
For iCol1 = 1 To 17
For iCol2 = 1 To 17
.Cells(iCol1, iCol2).Value = WorksheetFunction.Pearson( _
rInp.Columns(iCol1).Value, _
rInp.Columns(iCol2).Value)
Next iCol2
Next iCol1
End With
End Sub``````

#### AlphaFrog

##### MrExcel MVP

Need the Set keyword. Sorry.
Code:
``[COLOR="Red"]Set [/COLOR]myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))``

Or this...
Code:
``````With Worksheets("All")
[COLOR="Red"]Set[/COLOR] myrange1 = .Range(.Cells(1, rowcount), .Cells(EndRow, rowcount))
End With``````

#### Stebaker

##### New Member
Thanks again for replies...

shg4421 your code works great, apart from when the user input = 1 there is a time out code 1004 on the line:

Code:
``````.Cells(iCol1, iCol2).Value = WorksheetFunction.Pearson( _
rInp.Columns(iCol1).Value, _
rInp.Columns(iCol2).Value)``````

Seems a strange error, and can't understand why it occurs???¿¿¿ Otherwise perfect, thanks a lot! Cheers

Steve

#### shg

##### MrExcel MVP
That's curious -- 1 is the only value I tested with, and it worked fine.

All the values in the input range must be numbers.

#### Stebaker

##### New Member
Resolved... first 7 rows of series data missing in "All" - empty rows deleted and this now works perfectly as per your testing!

Thanks again for your help, awesome... S Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,657
Messages
5,838,632
Members
430,558
Latest member
Krampus ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back