Hi guys! I need help with 2 things with this code
this code gives me the residuals from a linear regression, I need to change to the standard residuals instead
this code gives me the residuals from a linear regression, I need to change to the standard residuals instead
VBA Code:
Sub testRegression()
Dim sh As Worksheet, celRes As Range, lastRowR As Long, rngDel As Range
Dim rngTarg As Range, rngDat As Range, rngRet As Range, arrRes
Set sh = ActiveSheet
Set rngTarg = sh.Range("A2:A201") 'the target range
Set rngDat = sh.Range("B2:B201") 'values to be processed
Set rngRet = sh.Range("G2") 'where to return (Regress function)
Application.Run "ATPVBAEN.XLAM!Regress", rngTarg, rngDat, False, _
False, , rngRet, True, False, False, False, , False
Set rngDel = Selection 'the returned range area is selected and
'take advantage of this aspect
'find the cell keeping the string "Residuals":
Set celRes = rngDel.Find(What:="Residuals", After:=rngRet, _
LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not celRes Is Nothing Then
'determine the last row of the "Residuals" column:
lastRowR = sh.Cells(sh.Rows.Count, celRes.Column).End(xlUp).Row
'put "Residuals" values in an array (header included):
arrRes = sh.Range(celRes, sh.Cells(lastRowR, celRes.Column)).Value
rngDel.Clear 'clear the initial returned range
With rngRet.Offset(-1, -1)
'drop the array values at once:
.Resize(UBound(arrRes), UBound(arrRes, 2)).Value = arrRes
.Select
End With
End If
End Sub