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][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``````
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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?

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))``

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.

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

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

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

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

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.

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
8
Views
711
Replies
0
Views
142
Replies
5
Views
209
Replies
1
Views
407
Replies
8
Views
962

1,214,767
Messages
6,121,423
Members
449,032
Latest member
egspen2

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.

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