Unable to get the Pearson Property of the worksheetfunction class


Well-known Member

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)


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

? arg1.address

? arg2.address


Well-known Member
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


Well-known Member
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


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


Well-known Member
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


Well-known Member
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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...