Thread: Runing "Sample" add-in within a macro

Hi there, long time reader first time poster...

Excel 2007, Data- Analysis add-ins installed.

I'm recording a macro to do gobs of copy/paste and column fills for me (filling a data analysis spreadsheet for different datasets in the same format), and interspersed in these copy/paste/autofill actions I occasionally recorded myself using the Sample add-in to make a list of every 6th entry in a column.

When I run the macro, all begins as it should, until it gets to the first time I ran the add-in. When it gets there, I get the following error message:

"Run time error '1004'. Method 'run' of object '_Application' failed"

I have a suspicion that this has something to do with which window or button is active (based on my searching around) but I'm hoping someone can take a look at my code and give me something to go on.

Here 'tis (note, its the first few lines of a LONG macro):

Sub AnalyzeStorm()
'
' AnalyzeStorm Macro
'
' Keyboard Shortcut: Ctrl+f
'
    Range("L2:L3").Select
    Selection.AutoFill Destination:=Range("L2:L2706")
    Range("L2:L2706").Select
    Columns("C:C").Select
    Selection.Copy
    Sheets("Rainfall").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Range("B2:B13").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("B2:B2706")
    Range("B2:B2706").Select
    Range("C2:C13").Select
    Selection.AutoFill Destination:=Range("C2:C2706")
    Range("C2:C2706").Select
    Range("D3:D13").Select
    Selection.AutoFill Destination:=Range("D3:D2706")
    Range("D3:D2706").Select
    Range("E2:E13").Select
    Selection.AutoFill Destination:=Range("E2:E2706")
    Range("E2:E2706").Select
    Range("F2:F13").Select
    Selection.AutoFill Destination:=Range("F2:F2706")
    Range("F2:F2706").Select
    ActiveWindow.SmallScroll Down:=-3
    Range("G2").Select
     Application.Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range("$F:$F"), _
        ActiveSheet.Range("$G$2:$G$2000"), "P", 6, True[/B][/B][/SIZE][/SIZE]
    ActiveWindow.SmallScroll Down:=-6
    Range("H2").Select
     Application.Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range("$C:$C"), _
        ActiveSheet.Range("$H$2:$H$2000"), "P", 6, True
    ActiveWindow.SmallScroll Down:=9
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 64

Its the

ActiveSheet.Range("$G$2:$G$2000"), "P", 6, True[/B][/B][/SIZE][/SIZE]
    ActiveWindow.SmallScroll Down:=-6

that causes the error.

I've subscribed to the thread, so I'm ready to try any suggestions.

I've since changed the macro to autofill any range with

rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))

I've also cleaned it up some, and re-ordered some operations.

Still the same error the first time it calls the application.

Sure. Y'all will probably have a better solution than what I came up with anyway.

I'm constructing a spreadsheet to process data from 8 water flow gauges logged every 10 seconds, round the clock. An event that I might want to graph could last as long as 24 hours, so I'm way oversampled for something of that length (conversely, usable events can be shorter than a half hour, so the resolution is good to have). The ultimate output is a graph showing the relative sum of these sensors' recordings over time.

For the longer events, I'd like to sum my 10 second data points into one minute data points (or even coarser for a super long event) so that excel has to crunch fewer numbers to display a graph.

My solution to this thusfar has been to create a column next to the data I'm interested in binning, and summing the same six cell blocks of data six times, then summing the next six cells of data six times and so on. For example 2 interations might look like this (column A is my data, colmn B is my binned data):

A B
1 9
2 9
3 9
2 9
1 9
0 9
0 2
1 2
0 2
1 2
0 2
0 2

After column B is auto-filled, I was trying to use the 'Sample' Analysis Tool to sample every sixth point from column B and produce a column C which would effectively be my new, binned data, ready for graphing.

I hope that clears things up, the issue was that although I was able to do this manually while the macro was recording, its having trouble replicating it. I'm sure there's a better workaround, but you work with the tools you know...right? I'm interested to see what other solutions exist.

Thanks, all.

Anyone? I'm really stuck here, surely there's a different way to accomplish this, or a way to fix the code...