VBA code ==> For each within time data series + correlation worksheet function

vba_no

New Member
Joined
Nov 30, 2015
Messages
1
Good day,

Had to return to VBA coding because I'm a bit embarrassed these days, and want to save time. It's been a long time that I didn't code and I can't find the solution. I'm requesting your help.
I have basically 3 columns, one for a data set(daily), one for a price_1 and the other one for price_2

What I want is to create a correlation matrix per month.
Example: For each month (MMMM) and year(YYYY) in my dataset, put me the right month correlation.

Note: the date 11/30/2015 is on range("A4")


DateP_1P_2
11/30/2015166
11/27/2015265
11/25/2015565
11/24/20158965
10/30/2015144611546
10/29/201511451164
10/28/20151456875

<tbody>
</tbody>

Nov Correlation-0.3588
Oct Correlation-0.05056

<tbody>
</tbody>

So basically, to compute correlation I use =correl()

Now here's my code. I can't find any solution to manage to get the good result:

Sub Correlation_matrix()


Dim Column_A, Column_B, Column_C, Date_reference_A, Date_reference_B, Date_reference_C, Selection_B, Selection_C As Range
Dim columnA_cell As Range


'Dim selection_cells As Range
Dim result_cell As Range
Dim n As Integer
Set Date_reference_A = Range("A4")
Set Date_reference_B = Range("B4")
Set Date_reference_C = Range("C4")
Set result_cell = Range("F5")


Set Column_A = Range(Date_reference_A, Date_reference_A.End(xlDown))
Set Column_B = Range(Date_reference_B, Date_reference_B.End(xlDown))
Set Column_C = Range(Date_reference_C, Date_reference_C.End(xlDown))


For Each columnA_cell In Column_A
For n = 0 To 22
If Month(columnA_cell) <> Month(columnA_cell.Offset(n, 0)) Then
'Set Selection_B = Range(Column_B.Offset(0, 0), Column_B.Offset(n, 0))
'Set Selection_C = Range(Column_C.Offset(0, 0), Column_C.Offset(n, 0))


result_cell = WorksheetFunction.Correl(Column_B, Column_C)


End If
Next




Exit For
Next
End Sub


Thanks for your kind help,
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

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
Back
Top