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,681
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,681
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,681
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,078,495
Messages
5,340,704
Members
399,390
Latest member
newexcel12

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top