Copying Pairs of Data Columns & Calculating their Correlation Coefficient

[rwmill9716]

I have the following macro:

Sub Partition()
Dim LastCol As Long, Col As Long, LastRow As Long
Dim rData As Range

Application.ScreenUpdating = False
With Sheets("Raw Data")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rData = .Range("A1").Resize(LastRow, LastCol)
End With
With Sheets("Calculated Results")
For Col = 2 To LastCol
.Range("B6").Resize(LastRow).Value = rData.Columns(1).Value
.Range("C6").Resize(LastRow).Value = rData.Columns(Col).Value
.Range("B6").Resize(LastRow, 2).Sort Key1:=.Range("C7"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next Col
End With
Application.ScreenUpdating = True
End Sub

It copies the A column from tab "Raw Data" into tab "Calculated Results" below and runs through each of the other columns in "Raw Data" in turn writing their results in tab "Calculated Results". The macro ends with the 1st and last columns of "Raw Data" in tab "Calculated Results." I need to change the macro to do the following:

1. Instead of contrasting just "Raw Data" column 1 with all the other columns, I need it to cycle through all possible pairs, i.e., doing column 1 versus column 2 and above, then column 2 versus column 3 and above to column next-to-last versus column last (here columns 9 and 10, but these could run to 100 columns in tab "Raw Data"; Note, I refer here to data column labels 1-10 instead of column letters;

2. For each pair brought over to columns B and C of calculate a correlation coefficient (Correl(array 1, array 2)) and row titles in columns E through I row 7; each time a pair is brought over calculated these row 7 values then step down to copy the next set in row 8; for 10 columns of data, there will be 45 of these rows.

As it stands now with this macro, it copies the 1st column from "Raw Data" into column B of "Calculated Results", then cycles through the remaining columns of "Raw Data" to end up with the table below and only this pair's information in row 7.

Excel 2003
	B	C	D	E	F	G	H	I
6	AMC in CHMD	HMI in CHMD		Correlation	Pair
7	1	10		-0.927	1	10	AMC in CHMD	HMI in CHMD
8		1343.134
9	121.5152	1351.486
10	110.3554	1390.642
11	103.6267	1402.796
12	103.5135	1403.865
13	102.189	1404.508
14	101.5	1424.5
15	100.7319	1439.393
Calculated Results