# 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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### 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

ADVERTISEMENT

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

ADVERTISEMENT

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

Replies
2
Views
47
Replies
1
Views
118
Replies
3
Views
660
Replies
3
Views
63
Replies
7
Views
221

Threads
1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

### Share this page ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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