The following code runs 3 simple regressions for a range of data in column 3 against the range data in column 7, then 4 against 8, then 5 against 9; and gives a regression output in a separate worksheet called "Sheet2".
Looking at the Summary Output, the output values for the coefficient for the X variable and the number of observations are a little off from a manual regression output. On further investigation, the following code excludes the first row of data in row 5 and only regresses from row 6 down to the last cell within a column. I attempted to correct the code but no to avail. Can anyone please modify the following code so that macro starts the regression using inputs from row 5 down to the last cell with data in a column? Thanking you in anticipation.
Sub aaa()
Dim DataSH As Worksheet, OutSH As Worksheet
Dim rngY As Range, rngX As Range, rngOut As Range
Dim i As Long, j As Long
Set DataSH = Sheets("Data 2")
Set OutSH = Sheets("Sheet2")
OutSH.Cells.ClearContents
OutSH.Activate
For i = 3 To 5
'For j = 7 To 9
j = i + 4
Application.StatusBar = i & ":" & j
With DataSH
Set rngY = .Range(.Cells(5, i), .Cells(5, i).End(xlDown))
Set rngX = .Range(.Cells(5, j), .Cells(5, j).End(xlDown))
End With
Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Value = DataSH.Cells(5, i) & " : " & DataSH.Cells(5, j)
Set rngOut = Cells(Rows.Count, "A").End(xlUp).Offset(2, 0)
Application.Run "ATPVBAEN.XLAM!Regress", rngY, rngX, False, True, , rngOut, False _
, False, False, False, , False
'Next j
Next i
Application.StatusBar = False
End Sub
Bookmarks