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