I am having difficulty getting my VBA to run the worksheet function correctly. I have tried making all sorts of modifications with no luck. Below is an example of one of my many failed attempts. It reads fine down to the line were it calls the worksheetfunction.slope and spits out a "Run-time error '1004': Application-defined or object-defined error". I can get the function to work fine in the worksheet (Using "=SLOPE(Table1[@[-13.78]:[0.00]],1*Table1[[#Headers],[-13.78]:[0.00]]") but I need to call it through VBA and loop through the corresponding column and rows.
Sub Slope1()
Dim Y As Double, X As Double
Dim YL As Range, XL As Range
Dim YR As Range, XR As Range
Dim i As Integer, j As Integer
Dim mL As Double, mR As Double
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Graph")
With ws
Set XL = .Range(.Cells(31, 2), .Cells(31, 44))
Set XR = .Range(.Cells(31, 44), .Cells(31, 86))
For i = 32 To .Cells(3, 30).Value
'SLOPE calculator of linear regression line
Set YL = .Range(.Cells(i, 2), .Cells(i, 44))
Set YR = .Range(.Cells(i, 44), .Cells(i, 86))
For j = 2 To 86
If j < 45 Then
mL = WorksheetFunction.Slope(XL, YL)
Else
mR = WorksheetFunction.Slope(XL, YL)
End If
Next
.Cells(i, 88).Value = mL
.Cells(i, 89).Value = mR
Next
End With
Bookmarks