descriptive statistics

[Asche]

want to make a macro to do a 'descriptive statistics data analysis' of column b and put the results in m1 to n18.
basically doing this but in a macro.
add in= tools /data analysis/ descriptive stats

I have recorded it doing this, but it for some reason can't do what it wrote.

 

[Cindy Ellis]

Can you post the code? That might help us help you.
Cindy

 

[Asche]

Can you post the code? That might help us help you.
Cindy

I need the code that is what I am asking for.

Sub xlAddin() Dim objExcel As Excel.Application Set objExcel = CreateObject("Excel.Application") ' Opens the add-in, which is in the Analysis folder of the ' Excel Library Directory. objExcel.workbooks.Open (objExcel.Application.librarypath & _ "\Analysis\atpvbaen.xla") ' Runs the AutoOpen macro in the add-in objExcel.workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen) MsgBox objExcel.Application.Run("atpvbaen.xla!lcm", 5, 2) objExcel.Quit Set objExcel = Nothing End Sub

that is what I am told I am looking............

 

[iliace]

Error -see next post.

 

[Asche]

Error -see next post.

what next post......

 

[Cindy Ellis]

I recorded the actions Tools | Data Analysis | Descriptive Statistics, then chose my options, saved the macro, deleted the output just created, reran the recorded macro, and it worked fine. Here's the code that was recorded:

Sub Macro1()
     Application.Run "ATPVBAEN.XLA!Descr", ActiveSheet.Range("$B$3:$B$9"), ActiveSheet.Range("$M$1"), "C", False, True, 1, 1, 95

Do you have the analysis toolpak activated?
Maybe we're missing the point.
Cindy

 

[Asche]

I recorded the actions Tools | Data Analysis | Descriptive Statistics, then chose my options, saved the macro, deleted the output just created, reran the recorded macro, and it worked fine. Here's the code that was recorded:

Sub Macro1()
     Application.Run "ATPVBAEN.XLA!Descr", ActiveSheet.Range("$B$3:$B$9"), ActiveSheet.Range("$M$1"), "C", False, True, 1, 1, 95

Do you have the analysis toolpak activated?
Maybe we're missing the point.
Cindy

not really sure, I know nothing really of excel I do know visual basic but not like this, I have win 2000 so if that helps. but let me try this then get back to you.

told me
'run time error 1004
atpvnane.xla could not be found. check the spelling of the file name and verify that the file located is correct.

if you are trying to open the file from your list of most recently used files on the file menu make sure that the file has not been renamed mover or deleted.'

'Application.Run "ATPVBAEN.XLA!Descr", ActiveSheet.Range("$B$1:$B$30"), _
'ActiveSheet.Range("$M$1:$N$18"), "C", False, True, 1, 1, 95

that is what my computer recorded

 

[Asche]

bump

 

[Cindy Ellis]

In the Tools menu, click Add-Ins, and make sure that Analysis Tookpak - VBA has a check-mark. If you don't see it in the list, then there may be something non-standard in your installation. (I'm on Excel 2000 as well, so that's not the issue).
All I did to create the code was to start the macro recorder, then "click through" the steps to get the descriptive statistics that I wanted, and to put them in M1. (Note the data range will need to be modified to fit your data...I just grabbed a few cells from column B).
Hope this helps,
Cindy

 

[Asche]

thanks that was the problem