Hi mikedjp
Welcome to the board
Assuming the data is in columns A:B and starts in row 2:
Code:
Sub correl()
Dim ans As Double
Dim rRngA As Range
Dim rRngB As Range
Set rRngA = Range("A2", Range("A2").End(xlDown))
Set rRngB = rRngA.Offset(, 1)
ans = Application.WorksheetFunction.correl(rRngA, rRngB)
MsgBox ans
End Sub
You can test it for ex. with (I used the worksheet formula to test the result):
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</tr><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</tr></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
1</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" style="border-width: 1px;border-color:#888888" >X</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" style="border-width: 1px;border-color:#888888" >Y</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
2</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >12</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:left" style="border-width: 1px;border-color:#888888" >Correl</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >0.961054</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
3</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >4</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >13</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
4</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >5</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >16</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
5</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >17</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
6</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" >18</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF; text-align:center" >
7</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:right" style="border-width: 1px;border-color:#888888" > </td></tr><tr><td colspan=7 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>
<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >Addr</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >Formula</td></tr><tr><td colspan=2 style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="background:#9CF " >[Book1]Sheet2</td></tr><tr><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" style="text-align:center" >E2</td><td style="border-width:1px;border-color:#888888; padding-left:0.5em; padding-top:0.25em; padding-right:0.5em; padding-bottom:0.25em" > =CORREL(A2:A6,B2:B6) </td></tr></table>
Hope this helps
PGC