Unable to get the Pearson Property of the worksheetfunction class

[SBF12345]

Greetings,

I am receiving the error "Unable to get the Pearson Property of the WorksheetFunction Class" Runtime Error on the line below.

Arg1 and Arg2 are both Range variables. J, and i are integer variables.

I would like the value of Cells(j,i) to be the value of the Pearson WorksheetFunction.

Workbooks("Matrix.xls").Worksheets("Paper2").Cells(J, i) = Application.WorksheetFunction.Pearson(arg1, arg2)

Thanks!

 

[shg]

How about WorksheetFunction.Correl(arg1, arg2)

 

[SBF12345]

It still returns an error with the word "Correl" instead of "Pearson"

 

[shg]

What do these return in the Immediate window when the code breaks?

? arg1.address

? arg2.address

 

[SBF12345]

Here is the whole script, to put things into context:

Sub CMatrixUpdate()

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




c = Workbooks("Matrix.xls").Worksheets("Paper2").Cells(1, Columns.Count).End(xlToLeft).Column


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


            a = J + 4
            b = i + 4


            e = Cells(Rows.Count, a).End(xlUp).Row
            f = Cells(Rows.Count, b).End(xlUp).Row


            With Workbooks("LOGREG_INPUT.xls").Worksheets("Paper1")
            
                Set arg1 = .Range(.Cells(2, a), .Cells(e, a))
                Set arg2 = .Range(.Cells(2, b), .Cells(f, b))
            
            End With


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


End Sub

 

[shg]

Yes, but my question was ...

 

[SBF12345]

I looked at them in the watch window and rng1.address and rng2.address are both reading "$F$1:$F$2". This looks like the basis of the error

 

[shg]

That would only result in an error if F1=F2. Otherwise, it would return 1.

 

[SBF12345]

it looks like the e and f variables are not being assigned correctly. I surrounded the e and f assignment with a "With statement but am still receiving values of 1 according to the watch

 

[SBF12345]

I shouldn't have used the word error in the earlier post.

Column F on the worksheet has about 2500 rows of data and so it seems odd to read range values like those described in the watch window