Correlation Matrix Macro returning blank values

[SBF12345]

Greetings,

I have a correlation matrix macro that I use to find correlation values for pairs of data. The macro seems to be working fine, but some of the matrix cell values are left blank. The blank sections are associated with specific parameters and hence leave entire columns and rows blank. I am having trouble understanding why this is occurring? ideas?

Below is the matrix code, although I don't think this is the source of error in this case.

Sub CMatrixUpdate()

Dim i As Integer
Dim J As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim arg1 As Range
Dim arg2 As Range


c = Workbooks("Matrix.xls").ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
d = Workbooks("Matrix.xls").ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


For J = 2 To c
    For i = 2 To d


            a = J + 4
            b = i + 4


            e = Workbooks("COMM_COMBINED_INDEPENDENTS.xls").ActiveSheet.Cells(Rows.Count, a).End(xlUp).Row
            f = Workbooks("COMM_COMBINED_INDEPENDENTS.xls").ActiveSheet.Cells(Rows.Count, b).End(xlUp).Row
            
            With Workbooks("COMM_COMBINED_INDEPENDENTS.xls").ActiveSheet
            
                Set arg1 = .Range(.Cells(7, a), .Cells(e, a))
                Set arg2 = .Range(.Cells(7, b), .Cells(f, b))


            End With
            
            On Error Resume Next


            Workbooks("Matrix.xls").ActiveSheet.Cells(J, i) = WorksheetFunction.Correl(arg1, arg2)
    
    Next i
Next J


End Sub

Thanks!

 

[StephenCrump]

I'd start by taking out the On Error Resume Next statement, so you can see what's really happening ...

As written, your code will error if arg1 and arg2 have a different number of data points, which in theory is possible given you have used two different variables e and f to defined their sizes.

Is there a single column in COMM_COMBINED_INDEPENDENTS.xls which will always be fully populated, and which you can use to determine the (same) sizes of arg1 and arg2? (And I'd prefer to define the worksheet name rather than relying on ActiveSheet).

 

[SBF12345]

Hi,

I included the slightly adjusted code with suggested edits converted to notes.

When running the code I errored (at the first cell which had previously been left blank when the on error was included in the script). The error was of the type "Unable to get the Correl property of the worksheetfunction class".

As for the fully populated column I just used the "a" variable column. This should work fine; all columns are of equal length. My intention in using ActiveSheet was so that I could collect data from a variety of different sheets. In retrospect, the whole macro looks like it would benefit significantly as a User Defined Function so that input ranges would gain in flexibility and output locations would gain in flexibility. I actually just ran into a problem with a UDF output for a correl matrix and put up a new mrexcel post under a different name "User Defined Functions ... trouble with output values"

Sub CMatrixUpdate()

Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim arg1 As Range
Dim arg2 As Range


c = Workbooks("Matrix.xls").ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
d = Workbooks("Matrix.xls").ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


For j = 2 To c
    For i = 2 To d


            a = j + 4
            b = i + 4


            e = Workbooks("COMM_COMBINED_INDEPENDENTS.xls").ActiveSheet.Cells(Rows.Count, a).End(xlUp).Row
            'f = Workbooks("COMM_COMBINED_INDEPENDENTS.xls").ActiveSheet.Cells(Rows.Count, b).End(xlUp).Row
            
            With Workbooks("COMM_COMBINED_INDEPENDENTS.xls").ActiveSheet
            
                Set arg1 = .Range(.Cells(7, a), .Cells(e, a))
                Set arg2 = .Range(.Cells(7, b), .Cells(e, b))


            End With
            
            'On Error Resume Next


            Workbooks("Matrix.xls").ActiveSheet.Cells(j, i) = WorksheetFunction.Correl(arg1, arg2)
    
    Next i
Next j


End Sub

 

[StephenCrump]

I actually just ran into a problem with a UDF output for a correl matrix and put up a new mrexcel post under a different name "User Defined Functions ... trouble with output values"

Yes, I saw that. There doesn't seem much point persisting here if you are going to open similar new threads.