Regression in VBA

[willie447]

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

[acw]

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.

[willie447]

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!

[acw]

Hmm

Worked yesterday, but not today. Try this one.

Tony

[willie447]

it works!! Thanks!!