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,721
Office Version
2010
Platform
Windows
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,721
Office Version
2010
Platform
Windows
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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,721
Office Version
2010
Platform
Windows
Yes, but my question was ...
 

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,721
Office Version
2010
Platform
Windows
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,084,751
Messages
5,379,634
Members
401,616
Latest member
YoSquidly

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top