Unable to get the Pearson Property of the worksheetfunction class

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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.

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
How about WorksheetFunction.Correl(arg1, arg2)
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
It still returns an error with the word "Correl" instead of "Pearson"
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
What do these return in the Immediate window when the code breaks?

? arg1.address

? arg2.address
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Here is the whole script, to put things into context:

Code:
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
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
That would only result in an error if F1=F2. Otherwise, it would return 1.
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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

Well-known Member
Joined
Jul 26, 2014
Messages
614
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
 

Forum statistics

Threads
1,077,849
Messages
5,336,736
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top