Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

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

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

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

    This is not what's causing your problem (ATM, anyway), but you are calculating e and f separately, and they must be the same; Correl (and Pearson) require equal-size arrays.

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

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

    And the assignment of e and f needs to be in that With block, for the same reason as before.

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

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

    And, unless your heart is set on VBA, this would be easy to do with formulas.

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

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

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Data 1
    Data 2
    Data 3
    Data 4
    Data 1
    Data 2
    Data 3
    Data 4
    2
    70
    77
    79
    88
    Data 1
    1
    -0.19294
    0.445674
    0.238539
    G2: =CORREL(A$2:A$21, INDEX($A$2:$D$21, 0, ROWS($G$1:G1)))
    3
    54
    48
    18
    40
    Data 2
    -0.19294
    1
    0.115421
    0.273441
    4
    90
    95
    99
    74
    Data 3
    0.445674
    0.115421
    1
    0.312972
    5
    6
    79
    54
    37
    Data 4
    0.238539
    0.273441
    0.312972
    1
    6
    100
    15
    80
    47
    7
    86
    42
    91
    60
    8
    57
    84
    97
    90
    9
    5
    39
    21
    92
    10
    1
    45
    51
    5
    11
    90
    24
    68
    86
    12
    25
    96
    82
    65
    13
    14
    74
    20
    33
    14
    1
    77
    40
    19
    15
    91
    59
    10
    18
    16
    69
    31
    91
    16
    17
    1
    34
    7
    53
    18
    13
    57
    85
    55
    19
    59
    30
    52
    67
    20
    61
    98
    81
    96
    21
    60
    1
    88
    30

  5. #15
    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

    When I watch the code and step through it with F8 the variable "a" reads "expression not defined in context" in the value area...further over in the context area it reads VBAProject.

    This is different from the other variable "b" which is very similar and if functioning accordingly. The primary difference between the two is the "context". "b" reads "VBAProject.Module84.CMatrixUpdate"

    How can I change the context?

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

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

    Does post #14 essentially show what you're trying to do?

  7. #17
    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

    yes, precisely

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

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

    Code:
    Sub CMatrixUpdate()
      Dim rInp          As Range
      Dim rOut          As Range
      Dim sArg1         As String
      Dim sArg2         As String
      Dim sArg3         As String
    
      Set rInp = Workbooks("LOGREG_INPUT.xls").Worksheets("Paper1").Range("A2")
      Set rInp = Range(rInp, rInp.End(xlDown).End(xlToRight))
    
      Application.Goto rInp
      Stop  ' does that look correct?
    
      With rInp.Columns
        Set rOut = Workbooks("Matrix.xls").Worksheets("Paper2").Range("A2").Resize(.Count, .Count)
      End With
    
      Application.Goto rOut
      Stop  ' does that look correct?
    
      With rInp
        sArg1 = .Columns(1).Address(RowAbsolute:=True, ColumnAbsolute:=False, External:=True)
        sArg2 = .Address(RowAbsolute:=True, ColumnAbsolute:=True, External:=True)
        sArg3 = rOut.Cells(1).Address(RowAbsolute:=True, ColumnAbsolute:=True, External:=False) & ":" & _
                rOut.Cells(1).Address(False, False)
        rOut.Formula = "=CORREL(" & sArg1 & ", INDEX(" & sArg2 & ", 0, ROWS(" & sArg3 & ")))"
        ' rOut.Value = rOut.Value ' uncomment this line if you just want values
      End With
    End Sub

  9. #19
    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

    Thanks for the input...ill have to spend more time with the chunk above to see if I can get it running.

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
  •