Excel 2007 : Need VBA formula to average dynamic ranges by column

[TucsonJack]

My reports present their numerical data across the rows for columns D - AH
Using VBA macro, I need to insert a formula in each of the blank cells separating the ranges in order to average the range of cells above it by column. The ranges will vary in the number of rows between reports and can contain text. If needed I can add more blank rows between data sets.
D E F G etc....
4 4 5 2
2 5 n/a 4
_ _ _ _ <-- insert formulas in this row to average the range above in each column
3 2 4 5
2 n/a 4 3
9 3 3 2
_ _ _ _ <-- insert formulas in this row to average the range above in each column
and so on....

When all formulas are copied to the blank cells, each range then gets copied and pasted into its own worksheet; all ranges then begin with cell D2. (I have that code working). I need the formula to be copied with the data and work in the new worksheets.

The macro could simply fill the formula into all blank cells in the greater range D1-AH200 (preferred), or it could do it by columns, as I've tried with no success....

Please Login or Register  to view this content.

Any help will be greatly appreciated. Thanks.

[foxguy]

I'm doing this on the fly without testing, so back up first.

Assuming that each group that needs to be averaged is 4 rows

Please Login or Register  to view this content.

The ".End(xlUp).Row + 1" needs the "+ 1" to put a formula below the last row of data.

[TucsonJack]

Foxguy,

You are a genius. The routine ran beautifully, except foryour assumption that I needed only 4 rows averaged. That was the only hiccup. The ranges are of variable length within each report. If someone needed to do this with fixed ranges, they'd be set with your solution.

Thanks for helping me out..

BTW .. I modified your code when I realized it could also be used to error-check the raw data for blank cells. It finds and fills them all, which had been a real headache! Thanks again for your excellent solutions.

[TucsonJack]

Forgot to rate your solution...

[foxguy]

I wondered whether it would always be 4 rows, but your example showed the Average function was always on the same row for all the columns and the 2 groups you showed both had 4 rows.

It will take a little more time to have it determine how many rows to average. I have to run an errand. I'll be back in about an hour and set it up.

[TucsonJack]

Foxguy,

I really appreciate your effort. I discovered one other thing... Because the formula uses $_$_, when my parsing routine copies and pastes each range to its own worksheet, the formula is likely to reference empty cells, resulting in a DIV/0 error. For example, in the source sheet, a range formula at D80, when pasted into its new worksheet still references $D$80 when it is now located at D9. Every range of data, when pasted, begins in the new sheet at D2.
Sorry, this is much more complex than I thought.

[foxguy]

Please Login or Register  to view this content.

[protonLeah]

My 2¢:

Please Login or Register  to view this content.

[foxguy]

Ben;

A minor suggestion.

When I want to compile a list of comma separated strings, I do it this way.

Please Login or Register  to view this content.

Then I strip off the 1st comma

Please Login or Register  to view this content.

That is very slightly faster because there is only one calculation (Mid) instead of 2 (Left & Len).

[TucsonJack]

Foxguy,

I get an error message: "Compile Error; Syntax error" on this line:

r.Formula = "=AVERAGE(Offset(" & s "))"

Thanks,

[TucsonJack]

ProtonLeah,

Thanks for your solution. It works exactly as needed! I have a number of directors who must fill out innumerable grading sheets: multiple students, multiple assessment items, multiple sites. They are going to love this automated spreadsheet when I get it complete. Thank you very much for your help.

TucsonJack