Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ¤trow).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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining a Range using Variables | Excel Discussion (Misc queries) | |||
Adding a range with three variables | Excel Discussion (Misc queries) | |||
Can I use variables in a Range function? | Excel Discussion (Misc queries) | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions |