+ Reply to Thread
Results 1 to 3 of 3

Modify regression code from row 5 down to the last cell with data in a column

Hybrid View

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

    Modify regression code from row 5 down to the last cell with data in a column

    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
    Attached Files Attached Files
    Last edited by csmith-han; 06-20-2016 at 09:50 PM. Reason: Re-upload attachment

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Modify regression code from row 5 down to the last cell with data in a column

    The issue related to the exclusion of first row is on this row of your code:
    Application.Run "ATPVBAEN.XLAM!Regress", rngY, rngX, False, True, , rngOut, False _
            , False, False, False, , False
    This TRUE sets "LABEL" as TRUE, implying the range in rngY and rngX variables has a label/heading in the first row, and therefore is excluded.

    Simply change this TRUE to FALSE. The rest of the code is fine.
    HTH!

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

    Re: Modify regression code from row 5 down to the last cell with data in a column

    Dear jewelsharma, great advice. Thank you for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Modify code to duplicate only rows with specific data/value in Column A
    By jealoui in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2015, 10:07 AM
  2. Modify code to add column that pulls cell data from second workbook
    By Bikeman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2014, 02:34 PM
  3. [SOLVED] Modify code to erase data when another cell is cleared
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 8
    Last Post: 12-15-2013, 11:06 PM
  4. VBA is inserting formula instead of cell data? Modify code, how?
    By ScubaGuy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2013, 10:55 AM
  5. [SOLVED] Modify this code to select different column
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-17-2012, 11:52 AM
  6. Modify VBA code to use column data
    By ArtZ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2012, 03:12 PM

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