Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Regression in VBA

  1. #1
    Join Date
    19th September 2005
    Posts
    49

    Regression in VBA

    Hello,

    FYI: A sample of some of the data I am working with is attached.

    I used VBA a couple times in school and didn't really understand much, so I basically only know that it exists. I hope someone can help me with an assignment I have for work, because I'm definitely struggling. I've done the necessary analysis in Minitab, but now my supervisor wants a program in Excel since he doesn't know how to use Minitab. So here it goes....

    This has to do with doing regression analysis to determine the variance in different accounts based upon numerous variables. First, I have monetary figures (dependant variable) for three years, listed by month under each account name. I also have six possible independant variables also listed by month.

    I am hoping to do the following:
    1. Have the ability to add data at the end of every month to the columns.
    2. Be able to pick the dates (from-to) to use in the regression (combo box).
    3. Be able to pick the independant variables to use in the regression (from just one to all in combo box).
    4. The regression needs to be able to exclude the variables that don't have a p-value of at least 90% significance.
    5. Be able to pick the dependant variable to be analyzed (ability to select multiple variables at once, resulting in multiple outputs would be ideal).
    6. I need the following regression statistics to be displayed for each analysis: Adjusted R2, F-value with conditional formatting, F significance level, and independant variable p-value.

    I know that the chance of getting all this is slim, but does anyone at least have some advice on how to get started or where else to look?

    Thanks,

    desperate newbie
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by willie447; October 26th, 2005 at 07:21.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th May 2005
    Posts
    1,036

    Re: Regression in VBA

    Hi

    Have a look at the attached. It may go some way to meeting some of your requirements.

    Press the button, select the date range, make multiple selections from the Independent variable list, and one selection from the dependent list.

    It should copy across to the sheet Regression, the relevant data, then perform Excel's standard regression.

    At this stage, it will only allow selection of one dependent....

    See if that will help.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th September 2005
    Posts
    49

    Re: Regression in VBA

    Thanks for the help. I'm having a little trouble though. I get an error stating "Object variable or With block variable not set" and when I go to the debug screen, the following code is highlighted:

    startrow = Sheets("data").Range("a:a").Find(what:=Format(ComboBox1.Value, "mmm-yy")).Row
    lastrow = Sheets("data").Range("a:a").Find(what:=Format(ComboBox2.Value, "mmm-yy")).Row


    Any help is appreciated!!! Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    6th May 2005
    Posts
    1,036

    Re: Regression in VBA

    Hmm

    Worked yesterday, but not today. Try this one.

    Tony
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th September 2005
    Posts
    49

    Re: Regression in VBA

    it works!! Thanks!!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd February 2014
    Posts
    1

    Re: Regression in VBA

    Hi Ozgrid;

    I tried runing your Regression VBA Examplev2; but unfortunetly, I keep getting an error at the floolwing line below:
    findit = WorksheetFunction.Match(Sheets("regression").Cells(1, i).Value, arr, 0)

    Error read 'Unable to get the Match of the worksheetFunction class'

    Any idea as to why this is happening?
    Thanks
    Lesliev

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    3,557

    Re: Regression in VBA

    Hi Lesliev,
    Welcome to the forum. Please do not post questions in other peoples threads. This is known as thread hijacking. Please start your own thread for this question with a meaningful thread title. You can create a link back to this thread if you think it will be useful. Also can you add code tags to all code in your posts
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Golden Rules | Forum Rules | Freebie Stuff | Smallman Freebies

    Ozgrid Free Excel Training

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Regression in VB
    By tadomaitis in forum EXCEL HELP
    Replies: 1
    Last Post: July 31st, 2006, 15:43
  2. Excel regression
    By IanB in forum EXCEL HELP
    Replies: 2
    Last Post: June 10th, 2003, 22:52
  3. Non-linear regression
    By soniatankel in forum EXCEL HELP
    Replies: 2
    Last Post: April 25th, 2003, 23:15
  4. Regression Analisys
    By fapim in forum EXCEL HELP
    Replies: 1
    Last Post: April 22nd, 2003, 22:38

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
  •  
porno