Range Variables

[Daniel]

I wrote a macro to sort a range(A556). 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!

 

[Steve Smallman]

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