+ Reply to Thread
Results 1 to 14 of 14

Linest Function - Unable to get LinEst property of the WorksheetFunction class

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Linest Function - Unable to get LinEst property of the WorksheetFunction class

    Hello,

    I have a problem with the Linest function. I first filtered data from 2 different worksheets and copied them together in another sheet in order to apply the "Union" function to create ranges. Now I wanted to run the Linest function and it returns the following error:

    "RuntimeError 1004:
    Unable to get LinEst property of the WorksheetFunction class"

    Here is the code:

    Sub Regression()

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    Dim rX As Variant
    Dim rY As Variant
    Dim Stat As Variant



    For i = 2 To 101
    Count = 2
    Old_Count = 2

    For k = 2002 To 2013
    For l = 1 To 4
    For j = 2 To 2893
    Sheets("Excess_Return").Select
    If Cells(j, 1) = k And Cells(j, 2) = l Then
    Count = Count + 1
    End If
    Next j

    Sheets("Excess_Return").Select
    Range(Cells(Old_Count, i + 2), Cells(Count - 1, i + 2)).Select
    Selection.Copy
    Sheets("RegressionData").Select
    Cells(2, 1).Select
    Sheets("RegressionData").Cells(2, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("Daily_Fama_French_Factors").Select
    Range(Cells(Old_Count, 4), Cells(Count - 1, 6)).Select
    Selection.Copy
    Sheets("RegressionData").Select
    Cells(2, 2).Select
    Sheets("RegressionData").Cells(2, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("Daily_Fama_French_Factors").Select
    Range(Cells(Old_Count, 8), Cells(Count - 1, 8)).Select
    Selection.Copy
    Sheets("RegressionData").Select
    Cells(2, 5).Select
    Sheets("RegressionData").Cells(2, 5).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Sheets("Liquidity").Select
    Range(Cells(Old_Count, i), Cells(Count - 1, i)).Select
    Selection.Copy
    Sheets("RegressionData").Select
    Cells(2, 6).Select
    Sheets("RegressionData").Cells(2, 6).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    '''Defining dependent and independent variable

    rX = Range(Cells(2, 2), Cells(Count - Old_Count + 1, 6)).Value

    rY = Range(Cells(2, 1), Cells(Count - Old_Count + 1, 1)).Value

    '''Regression

    Stat = Application.WorksheetFunction.LinEst(rY, rX, True, True)


    Old_Count = Count
    Range(Cells(2, 1), Cells(100, 8)).Clear

    Next l
    Next k
    Next i

    End Sub



    Really would appreciate your help!

    Fabian

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    When using worksheetfunctions in vba you generally (maybe always) need to use ranges instead of values. So for example, if you were to use a function on a worksheet and you would use function(A1:B1), in vba you would need function(Range("A1:B1")). So in your code rY and rX need to be range objects so you need to change:
    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    I reckon you most likely have a data issue-you can use either ranges or arrays here
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    I changed it and it still gives the same Error.

    I attached the data which should be regressed. First column is rY, the ones after are rX.

    Can you see a problem there?
    Attached Files Attached Files

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    yes-columns B:E are stored as text. if you convert to number the code works

  6. #6
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    I marked everything and clicked on numbers and tried it again...doesn't work
    Or what do you mean by converting?

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    either use the smart tag that appears when you select the data or copy a blank cell, select your data and choose Paste Special, then Values and Add

  8. #8
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    That's exactly what I did...doesn't work out somehow. always the same error coming up. I also did it for every sheet where the data comes from.

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    can you provide a revised sample?

  10. #10
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    here's the updated file
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    I also tried to attach the worksheets I get the data from, but the file is too large to upload...

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    when you paste special after copying the blank cell, you are selecting Values and Add? do you have your error checking options set to show numbers stored as text?

    try adding this line to your code after pasting the data originally
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-25-2013
    Location
    Coventry, England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    IT WORKS Thank you so much Josie!

  14. #14
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linest Function - Unable to get LinEst property of the WorksheetFunction class

    you're welcome :-)

    please don't forget to mark the thread solved (click the 'thread tools' link at the top, then 'mark solved')

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Unable to get Match property of the WorksheetFunction class
    By pavu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-29-2012, 10:00 AM
  2. Unable to get countIF property of worksheetFunction class
    By Deamo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2010, 09:35 PM
  3. Unable to get the VLookup property of the WorksheetFunction class
    By JesseBurton in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 12-09-2009, 10:29 AM
  4. Replies: 2
    Last Post: 12-13-2007, 09:59 AM
  5. Unable to get the Vlookup property of the WorksheetFunction class
    By DoctorG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-17-2006, 02:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1