+ Reply to Thread
Results 1 to 13 of 13

Macros for regression

  1. #1
    Registered User
    Join Date
    08-23-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Macros for regression

    Hi,

    I am trying to create a Macros to run a series of regressions, but my VBA skills are not extensive. When I recorded the Macro for the first regression I got the below
    Please Login or Register  to view this content.
    Can Anyone tell me how this breaks down and where I can enter the X and Y ranges for the regression. I need to be able to put a seperate script in the ranges that selects all filled cells in the column for the ranges? I also need to know where I can enter the output range.

    Thank you so much for any help you can give me with this, I greatly appreciate it.

    Kind regards,

    Guy
    Last edited by romperstomper; 08-23-2010 at 08:15 AM. Reason: added code tags.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macros for regression (Excel 2007)

    Hi and welcome to the forum.
    Please use code tags when posting code - I have added them for you on this occasion.

    The first two arguments to the Regress routine are the Y and X inputs (in that order) and the 6th is the output range.
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    08-23-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macros for regression (Excel 2007)

    Hi Romperstomper,

    Thanks very much for your help (and sorting my post formatting). So would I be right in thinking it would go like this,
    Please Login or Register  to view this content.
    Thanks again for your help
    Last edited by romperstomper; 08-23-2010 at 08:33 AM. Reason: code tags again.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macros for regression (Excel 2007)

    No, it would be:
    Please Login or Register  to view this content.
    Note: I have done your code tags once again. you must do them yourself in future, per the forum rules, or you won't get any answers.

  5. #5
    Registered User
    Join Date
    08-23-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macros for regression (Excel 2007)

    Apologies I had tried to tag it but evidently didn't do it right, I think I have got it this time.

    I have now come up with the following code but am still having issues can you see any obvious (novice) errors in the below.

    Please Login or Register  to view this content.
    Thanks for your continued help with this,

    Guy

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macros for regression

    Several!
    Please Login or Register  to view this content.
    should be:
    Please Login or Register  to view this content.
    You haven't supplied any arguments at all to the Regress function but you have supplied them on the end of the final Goto command, which doesn't take them!

  7. #7
    Registered User
    Join Date
    08-23-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macros for regression

    Thanks again, I am rapidly realising this is beyond my skills with VBA,

    This is what I now have

    Please Login or Register  to view this content.

    Any closer than before..... I am still getting numerous errors with this

    Thanks

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macros for regression

    Try this:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-23-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macros for regression

    Actually I have now managed to get past a number of the errors and excel is giving me an error of "input Y range must be contiguous data" I have tried running that section of the code independently and it selects the right data and it is contigous. Any ideas where I am going wrong? New code below:

    Please Login or Register  to view this content.
    Could it be to do with the range ("B379).Select section?

    Thanks again

  10. #10
    Registered User
    Join Date
    08-23-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macros for regression

    Ignore my last, your code worked a treat.

    Thank you so much for all your help today, it has been greatly appreciated.

    Guy

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Macros for regression

    No problem. Please don't forget to mark the thread as 'Solved'.

  12. #12
    Registered User
    Join Date
    08-23-2012
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Macros for regression

    Hello I have the same problem in Excel 2010 and it doesn't work with this solution of romperstomer.

    Can anybody please Help me??

    here is the code:

    Please Login or Register  to view this content.
    Thanks
    Last edited by Cutter; 08-23-2012 at 11:57 AM. Reason: Added code tags

  13. #13
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Macros for regression

    Hello foukjods, and welcome to the forum.

    Unfortunately you have inadvertently broken two of the forum rules. Please read the following. Thanks.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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