Range Variables
 

I wrote a macro to sort a range(A5:P56). Col A contains
a date)and Col's B-P contains marketing data for the week.
Rows 5-56 contain the records for each week of the year.
The workbook contains 10 worksheets, one worksheet for the
past 10 years. The macro inserts 2 rows below the last
week of each month in the year I select from a dialog
box.
Now, I want to avg. the first 8 columns on the first blank
row under each month of the year and sum the rest of the
columns. I am stuck trying to get the macro to do this
for each worksheet since there are different months
containing 4 and 5 weeks of data to average or sum in
different years. I do not want to do a Pivot Table.

Getting frustrated and really needing help!

Range Variables
 

Daniel,

Are you inserting the formulae through code. If so, then you need to detect
where the next blank line above is to determine the height of the formula.

am I correct so far?

If you use a loop and use activecell.offset, you can test the contents of a
cell, and determine if it contains a value or is blank.

e.g.
currentrow=activecell.row
Do until activecell.offset(0,-1)=""
activecell.offset(0,-1).select
loop
newfirstrow=activecell.row
range(activecell.column &currentrow).select


An alternative you may wish to try is using the Data\Subtotals menu item.
This will give you subtotals using the average and the sum function. A
suggestion as an alternative method only.

Good Luck

Steve

"Daniel" wrote in message
...
I wrote a macro to sort a range(A5:P56). Col A contains
a date)and Col's B-P contains marketing data for the week.
Rows 5-56 contain the records for each week of the year.
The workbook contains 10 worksheets, one worksheet for the
past 10 years. The macro inserts 2 rows below the last
week of each month in the year I select from a dialog
box.
Now, I want to avg. the first 8 columns on the first blank
row under each month of the year and sum the rest of the
columns. I am stuck trying to get the macro to do this
for each worksheet since there are different months
containing 4 and 5 weeks of data to average or sum in
different years. I do not want to do a Pivot Table.

Getting frustrated and really needing help!