+ Reply to Thread
Results 1 to 7 of 7

Using a macro in VBA to run regressions

Hybrid View

  1. #1
    Registered User
    Join Date
    06-15-2013
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Using a macro in VBA to run regressions

    I searched this forum and found this code to run many simple regressions at once (3600 of them) and send the output for all of them to a single worksheet. It runs a regression for the data in column 3 against the data column 65, then column 3 against column 66, etc., then column 4 against column 65, then column 4 against 66, etc., and so on. It works beautifully, but what I would like to do is run a regression for the data in column 3 against the data in column 65, then 4 against 66, then 5 against 67, and so on for a total of 60 regressions.

    Unfortunately, I can't seem to correctly modify the code in order to make that happen. Can anyone help me? Thanks in advance!

    Sub aaa()
      Dim DataSH As Worksheet, OutSH As Worksheet
      Dim rngY As Range, rngX As Range, rngOut As Range
      Set DataSH = Sheets("Data 2")
      Set OutSH = Sheets("Sheet2")
      OutSH.Cells.ClearContents
      OutSH.Activate
      For i = 3 To 63
        For j = 65 To 125
          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

  2. #2
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Using a macro in VBA to run regressions

    try with the indicated changes, primarily omit the j loop but put j always=i+62
    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 63
        'For j = 65 To 125
          j = i + 62
          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

  3. #3
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Using a macro in VBA to run regressions

    Just as a warning, this code assumes you have no missing data (ie, no blank cells), if you do, then the ranges need to be modified.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  4. #4
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Using a macro in VBA to run regressions

    this post not entered as intended. disregard ...
    Last edited by kalak; 06-16-2013 at 08:33 AM.

  5. #5
    Registered User
    Join Date
    12-09-2012
    Location
    Dunedin, New Zealand
    MS-Off Ver
    2010 and 2013
    Posts
    13

    Re: Using a macro in VBA to run regressions

    Dear Forum Guru, I stumbled this post three years later and am interested in the comment you posted here. Your reply to this post was "Just as a warning, this code assumes you have no missing data (ie, no blank cells), if you do, then the ranges need to be modified."

    I have a situation here where my regression is on columns of data that contain blank cells. How do you change the VBA code written by kalak to deal with blank cells in a column when running regressions?

    Thanking you in anticipation if you're still an blogger to EXCEL Forum.
    Last edited by csmith-han; 06-19-2016 at 01:54 AM. Reason: Typo

  6. #6
    Registered User
    Join Date
    06-15-2013
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Using a macro in VBA to run regressions

    kalak, you are a life saver. After seeing what you did, I was able to modify it even further to perform many multiple regressions as well. Thanks again!

  7. #7
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Using a macro in VBA to run regressions

    Quote Originally Posted by NoCover View Post
    kalak, you are a life saver. After seeing what you did, I was able to modify it even further to perform many multiple regressions as well. Thanks again!
    NoCover,

    You're very welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1