Regression with 'for'-loop

[Timi]

Hello,

I am trying to run the following regression with a 'for' loop.

Sub Betas()
    
    For i = 1 To 10
        Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Rt").Range(.Cells(2, i + 1), .Cells(246, i + 1)), _
            Sheets("Rm").Range(.Cells(2, i + 1), .Cells(246, i + 1)), False, False, , Worksheets("Regressionen").Range("$A$1") _
            , False, False, False, False, , False
        Sheets("Regressionen").Select
        Range("B18").Select 'copy Beta
        Selection.Copy
        With Sheets("Betas")
            .Cells(2, i).Paste
        End With
    Next i
    
End Sub

I get an error message that the reference is wrong. When I refer to other sheets i normally use the 'with' function, but in this case this is not possible. Can somebody help me to use the reference in the regression correctly?

Thanks
Timi

 

[VoG]

Try

Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Rt").Range(Sheets("Rt").Cells(2, i + 1), Sheets("Rt").Cells(246, i + 1)), _
            Sheets("Rm").Range(Sheets("Rm").Cells(2, i + 1), Sheets("Rm").Cells(246, i + 1)), False, False, , Worksheets("Regressionen").Range("$A$1") _
            , False, False, False, False, , False

 

[Timi]

Hey Peter,

thanks a lot, that works. But i have a new problem now:

For every regression I get warned that I am about to overwrite the range. That's quite annyoing if you want to make 3000 regressions. I already tried 'Application.DisplayAlerts ', but I still get noticed in every loop. Can I solve that elseway.

Sub Betas()
    
    Application.DisplayAlerts = False
    
    For i = 1 To 3
        Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Rt").Range(Sheets("Rt").Cells(2, i + 1), Sheets("Rt").Cells(246, i + 1)), _
            Sheets("Rm").Range(Sheets("Rm").Cells(2, i + 1), Sheets("Rm").Cells(246, i + 1)), False, False, , Worksheets("Regressionen").Range("$A$1") _
            , False, False, False, False, , False
        Sheets("Regressionen").Select
        Range("B18").Select 'copy Beta
        Selection.Copy
        Sheets("Betas").Select
        Cells(2, i).Select
        ActiveSheet.Paste
    Next i
    
    Application.DisplayAlerts = True
    
End Sub

Thanks again

 

[VoG]

Sorry, I don't know how to suppress that message if Application.DisplayAlerts=False doesn't work.

 

[Timi]

Well then I am looking forward to a lot of clicking, thanks anyway.

 

[xenou]

        Sheets("Regressionen").Select
        Range("B18").Select 'copy Beta
        Selection.Copy
        Sheets("Betas").Select
        Cells(2, i).Select
        ActiveSheet.Paste

maybe amend to:

        Sheets("Regressionen").Range("B18").Copy _
            Destination:=Sheets("Betas").Cells(2, i)

(?)

Also, by the way, I believe you can turn off the warning about overwriting cells - I *think* it's an option in Excel options (probably an application property as regards vba).

 

[Timi]

Thanks for the correcter code, always appreciate that since I am still quite unexperienced in VBA.

I couldn't find the option but I managed to solve it different:

Sub Betas()
    
    For i = 1 To 10
        Application.SendKeys "{ENTER}"
        Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Rt").Range(Sheets("Rt").Cells(2, i + 1), Sheets("Rt").Cells(246, i + 1)), _
            Sheets("Rm").Range(Sheets("Rm").Cells(2, i + 1), Sheets("Rm").Cells(246, i + 1)), False, False, , Worksheets("Regressionen").Range("$A$1") _
            , False, False, False, False, , False
        Sheets("Regressionen").Range("B18").Copy _
            Destination:=Sheets("Betas").Cells(2, i)
    Next i
    
End Sub

I actually wonder why that works, since I "press" 'Enter" before I run the regression. But when I do it the other way round (Application.SendKeys "{ENTER}" after the regression), it asks me once (for the first i) and then it works as well.

?Strange?

thx anyway