+ Reply to Thread
Results 1 to 13 of 13

Regression macro on multiple sheets, excluding certain sheets

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    Regression macro on multiple sheets, excluding certain sheets

    Hello everyone,

    New to the forums and relatively new to the Macro/VBA world. I'm working on a macro which runs regression analysis on a specific X/Y column on multiple tabs of data and drops the regression table output into each sheet. I have the following code working, but it's too clunky and I'd like to adjust the code to exclude specific sheets:

    Please Login or Register  to view this content.
    Sub Regression()
    Dim s As Worksheet
    For Each s In ActiveWorkbook.Worksheets
    '
    ' Regression Macro
    '
    ' Keyboard Shortcut: Ctrl+r
    '
    Application.Run "ATPVBAEN.XLAM!Regress", s.Range("$L$14:$L$43"), _
    s.Range("$P$14:$P$43"), True, False, , s.Range("$F$47") _
    , False, False, False, False, , False
    Next s


    End Sub
    Please Login or Register  to view this content.
    Here is my attempt at editing the code to run and exclude specific sheets:

    Please Login or Register  to view this content.
    Sub Regression()
    Dim ws As Worksheet
    For Each s In ActiveWorkbook.Worksheets
    If sh.Name = "0. Hedge Index" Or sh.Name = "1. Effectiveness Assessment" Or sh.Name = "IRS All Valuation Data" Or sh.Name = "Swap Key" Or sh.Name = "Immaterial FV at 2.1 -Bloomberg" Or sh.Name = "Tickmarks" Then
    ' Regression Macro
    '
    ' Keyboard Shortcut: Ctrl+r
    '
    Application.Run "ATPVBAEN.XLAM!Regress", s.Range("$L$14:$L$43"), _
    s.Range("$P$14:$P$43"), True, False, , s.Range("$F$47") _
    , False, False, False, False, , False
    End If
    Next s
    End Sub
    Please Login or Register  to view this content.


    I'm getting a 424 error, object required, on my 'if statement' above. Any help is greatly appreciated!!

    Thanks very much,

    -Spencer

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Regression macro on multiple sheets, excluding certain sheets

    Without knowing anything about regression analysis, in this line of code:
    Please Login or Register  to view this content.
    change all occurrences of 'sh' to 's'.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Regression macro on multiple sheets, excluding certain sheets

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    I will add them for you - this time
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Regression macro on multiple sheets, excluding certain sheets

    deleted my duplicate post

  5. #5
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    Re: Regression macro on multiple sheets, excluding certain sheets

    Thank you everyone! Apologies for posting my code without tags, and for posting duplicate threads - the server timed out as I was posting!

  6. #6
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    Re: Regression macro on multiple sheets, excluding certain sheets

    For some reason, the macro is still trying to run regression on the tabs I excluded. Is there anything I should change so the code properly excludes the tabs in my IF statement? Here is my updated code:

    Please Login or Register  to view this content.
    Thanks again,

    -Spencer

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Regression macro on multiple sheets, excluding certain sheets

    Maybe ...

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Regression macro on multiple sheets, excluding certain sheets

    I will throw this in as a possible simpler approach. The regression tool in the toolpack uses the same algorithms as the LINEST() function. If I were doing this, I would use the LINEST() function, and just have a copy of the function on those tabs that I wanted.

    Another advantage of using the worksheet function is that it will update automatically when new data is entered without the need to execute the macro to update the regressions. https://support.office.com/en-us/art...a-fa7abf772b6d
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    Re: Regression macro on multiple sheets, excluding certain sheets

    I'd like to thank everyone for the input and help on this.

    Mr. Shorty, I think that's a good call. I may consider that, but wanted to use a macro to be flashy

    shg - I was able to use your code and get it working with 1 modification (just needed to change the regression function a little bit). To close the loop on this, here is the updated code if anyone is curious:

    Please Login or Register  to view this content.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Regression macro on multiple sheets, excluding certain sheets

    You're welcome.

    Quote Originally Posted by MrShorty View Post
    If I were doing this, I would use the LINEST() function, and just have a copy of the function on those tabs that I wanted.
    That would be my recommendation as well.

  11. #11
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    Re: Regression macro on multiple sheets, excluding certain sheets

    Ok VBA gurus, I have a related question for you regarding a similar macro I've setup. I'm trying to setup a macro to clear all the data where the regression output goes. Here is what I'm working with, but having issues getting it to run on multiple tabs and delete the selection:

    Please Login or Register  to view this content.
    Appreciate everyone's help with this!!!

    -Spencer

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Regression macro on multiple sheets, excluding certain sheets

    In the "Case Else" part, the rows and range objects are unqualified, which, I think, means they refer to whatever worksheet is is "active" (which may or may not be the same as wks). I would suggest that you make sure to qualify all object references so that it is clear what object is being referred to. In this case, I think you mean wks.rows("47:68")...
    wks.range("A47")...

  13. #13
    Registered User
    Join Date
    02-10-2016
    Location
    Seattle, WA
    MS-Off Ver
    2010
    Posts
    8

    Re: Regression macro on multiple sheets, excluding certain sheets

    Thanks to everybody who contributed! Going to mark this as CLOSED now. Got the 'clear cells on specific tabs' macro working, in case anyone is curious, here is the code I ended up with:



    Please Login or Register  to view this content.

+ 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. Regression Macro to Run on All Sheets, Excluding Some
    By sfostrom in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2016, 09:22 AM
  2. Regression Macro to Run on All Sheets, Excluding Some
    By sfostrom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2016, 02:01 PM
  3. average on multiple sheets excluding zero
    By jayp_ in forum Excel General
    Replies: 11
    Last Post: 08-29-2014, 04:11 AM
  4. [SOLVED] Looping through sheets to clear a specified range, but excluding some sheets
    By d.sanchez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2013, 02:39 PM
  5. [SOLVED] Copy All Visible Sheets To New Workbook Excluding Specific Sheets
    By ezrizer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-19-2012, 02:19 PM
  6. Copy specific cell from multiple sheets to summary - excluding some sheets
    By kabammi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2012, 09:27 AM
  7. regression macro on multiple sheets
    By Garryk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 07:57 PM

Tags for this Thread

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