Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Unable to get the Pearson Property of the worksheetfunction class
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Posts
    614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Unable to get the Pearson Property of the worksheetfunction class

    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!

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

    How about WorksheetFunction.Correl(arg1, arg2)

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Posts
    614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

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

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

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

    ? arg1.address

    ? arg2.address

  5. #5
    Board Regular
    Join Date
    Jul 2014
    Posts
    614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

    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

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

    Yes, but my question was ...

  7. #7
    Board Regular
    Join Date
    Jul 2014
    Posts
    614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

    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

  8. #8
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

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

  9. #9
    Board Regular
    Join Date
    Jul 2014
    Posts
    614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

    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

  10. #10
    Board Regular
    Join Date
    Jul 2014
    Posts
    614
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unable to get the Pearson Property of the worksheetfunction class

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •