PDA

View Full Version : Macro for Descriptive Statistics fun! (calling ATPVBAEN.XLAM)



PureNovic:(
09-10-2015, 07:07 AM
Hi guys,

Very new here and very new to VBA so all help is incredibly appreciated.

I've been tasked with a little and probably rather simple project of running some Toolpak analytics on some data.
I have installed the add-ins and i have been able to get this to run on my source data rather easily.


Sub RunStatistics()

Sheets("Source").Select
Columns("A:A").Select
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$A:$A"), _
"Results", "C", True, True, 2, 2

End Sub

However as i need to turn this into a workbook for complete novices i need to do some tidy up in the code to make sure that sheets are being deleted etc. So if the Results sheet exists i want it deleted (causes error if not as is being used for the output of ATPVBAEN.XLAM)


The following code gets the error " Compile Error: Type Mismatch" on the function header Sub Cleanup() but i dont know why as im not passing anything in??


Sub Cleanup()

Dim sheet As Worksheet
Dim row_min As Integer
Dim row_max As Integer
Dim col_min As Integer
Dim col_max As Integer


' Select the used range.
Set sheet = "Results"
Range("A1:ZZ200").Select
Selection.Copy
Sheets("Desc_Results").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'now delete Results tab so can be used again
Sheets("Desc_Results").Delete

End Sub


Thanks everyone so much in advance, i appreciate the elementary nature of this question!

mancubus
09-10-2015, 07:25 AM
welcome to the forum

this:



Dim ws As Worksheet
Set ws = Worksheet("Results")


and you dont need Select's...



Sub Cleanup()
Worksheets("Results").Range("A1:ZZ200").Copy
Worksheets("Desc_Results").Range("A1").PasteSpecial Paste:=xlPasteValues
Worksheets("Results").Cells.ClearContents
'Worksheets("Results").Delete
End Sub

PureNovic:(
09-10-2015, 07:48 AM
thanks for the response.

At the Sub Cleanup() function i now get the message "Compile Error: Sub or Function not defined" ?

mancubus
09-10-2015, 07:58 AM
it is a typo. "s" of Worksheet"s" is missing. corrected in the macro.

and i would use
Worksheets("Results").Range("A1").CurrentRegion.Copy

rather than
Worksheets("Results").Range("A1:ZZ200").Copy

if it is a contiguous range.