How to perform a regression from within a programmed macro

[Sparky_]

I have worksheets of data in the same format (different data / same format) - I am needing to routinely needing to perform a regression analysis on this data.

A recorded macro (shortcut key) would be very helpful.

I have attempted and continue to strike out to record a macro that:
goes to data tab, selects data analysis, selects regression and so forth.

I get an error when running the macro: Run-Time error 1004
We couldn't find: ... atpvbaen.xlam

My recorded macro appears to be one (1) line of code

Can a recorded macro include a regression analysis?

Help?

Thanks
Sparky_

 

[offthelip]

open the visual basic window, (alt F11) then under tools select references, look down the list to find atpvbaen and tick the box. then try your macor again.

 

[Sparky_]

the list appears alphabetic. I do not appear to have that file or option. the only atp*** I have within the long list is ATP 2.0 Type Library

How do I add this or is there another problem?

Thanks

 

[offthelip]

It sounds like you need to include the analysis toolpack as an addin
go to File/optioins/Addins and select Excel addins and go then select the box against Analysis toolpack VBA. then do post #2 above

 

[Sparky_]

currently (meaning without touching it), I have Analysis ToolPak and Solver Add-ins checked.
Analysis ToolPak-VBA is not checked. .... Check that one?

Thanks

 

[offthelip]

Yes, Analysis Toolpak - VBa unchecked would definitely cause the "We couldn't find: ... atpvbaen.xlam"
you also need to go and find under VBA/tools/ reference as well, so don't forget to do that too.