Macro to extend formula with sum of every other two rows

**mmchung** · 01-18-2013, 02:50 AM

Hello…
I have two worksheets, one with monthly data (‘monthly’ sheet) and the second (‘quarterly’ sheet) with excel formula transferring monthly data into quarterly ones. I have added new rows in the second worksheet. Now I want to extend the formula used in the previous rows and do this via VBA. However, the formula is the sum of every other two rows, which makes the macro difficult to write.

For example, I have following formulas in the quarterly sheet:

.............Column A..........Column B
Row10... 2011-Q1... =SUM(Monthly!B5:B7)
Row11... 2011-Q2... =SUM(Monthly!B8:B10)
Row12... 2011-Q3... =SUM(Monthly!B11:B13)
Row13... 2011-Q4... =SUM(Monthly!B14:B16)
Row14... 2012-Q1... =SUM(Monthly!B17:B19)
Row12... 2012-Q2... =SUM(Monthly!B20:B22)
Row16... 2012-Q3... =SUM(Monthly!B23:B25)
Row17... 2012-Q4... =SUM(Monthly!B26:B28)

Now, I want to extend the formula for one more year as the following, highlighted in red:

.............Column A..........Column B
Row10... 2011-Q1... =SUM(Monthly!B5:B7)
Row11... 2011-Q2... =SUM(Monthly!B8:B10)
Row12... 2011-Q3... =SUM(Monthly!B11:B13)
Row13... 2011-Q4... =SUM(Monthly!B14:B16)
Row14... 2012-Q1... =SUM(Monthly!B17:B19)
Row15... 2012-Q2... =SUM(Monthly!B20:B22)
Row16... 2012-Q3... =SUM(Monthly!B23:B25)
Row17... 2012-Q4... =SUM(Monthly!B26:B28)
Row18... 2013-Q1... =SUM(Monthly!B29:B31)
Row19... 2013-Q2... =SUM(Monthly!B32:B34)
Row20... 2013-Q3... =SUM(Monthly!B35:B37)
Row21... 2013-Q4... =SUM(Monthly!B38:B40)

I have to do this for 30 workbooks and it is extremely helpful if I can just run the macros to do the work.

Help and thanks in advance!

**NickyC** · 01-18-2013, 03:24 AM

Hi
will the new formulas always refer to the same ranges (e.g. B29:B31) in each workbook, or will the rows and columns sometimes vary?

**mmchung** · 01-18-2013, 03:59 AM

The new formulas refer to the same range in each workbook.

However, the example above is a simplified version of my problem. In the quarterly sheet, there are actually more than 10 columns of formulas in addition to column B: some with the ‘sum’ function and others with the ‘average’ function.

.............Column A..........Column B..........Column C……….
Row10... 2011-Q1... =SUM(Monthly!B5:B7) ... =AVERAGE(Monthly!G5:G7) ……….
Row11... 2011-Q2... =SUM(Monthly!B8:B10) ... =AVERAGE(Monthly!G8:G10) ……….
Row12... 2011-Q3... =SUM(Monthly!B11:B13) ... =AVERAGE(Monthly!G11:G13) ……….
Row13... 2011-Q4... =SUM(Monthly!B14:B16) ... =AVERAGE(Monthly!G14:G16) ……….
Row14... 2012-Q1... =SUM(Monthly!B17:B19) ... =AVERAGE(Monthly!G17:G19) ……….
Row15... 2012-Q2... =SUM(Monthly!B20:B22) ... =AVERAGE(Monthly!G20:G22) ……….
Row16... 2012-Q3... =SUM(Monthly!B23:B25) ... =AVERAGE(Monthly!G23:G25) ……….
Row17... 2012-Q4... =SUM(Monthly!B26:B28) ... =AVERAGE(Monthly!G26:G28) ……….
Row18... 2013-Q1... =SUM(Monthly!B29:B31) ... =AVERAGE(Monthly!G29:G31) ……….
Row19... 2013-Q2... =SUM(Monthly!B32:B34) ... =AVERAGE(Monthly!G32:G34) ……….
Row20... 2013-Q3... =SUM(Monthly!B35:B37) ... =AVERAGE(Monthly!G35:G37) ……….
Row21... 2013-Q4... =SUM(Monthly!B38:B40) ... =AVERAGE(Monthly!G38:G40) ……….

**NickyC** · 01-18-2013, 04:26 AM

Hi
this macro should copy the formula in the existing cell and replicate it down in the next 4 cells for the next 4 quarters
if this seems to work, we can develop it to apply to all columns in the sheet.

Please Login or Register  to view this content.

**NickyC** · 01-18-2013, 04:33 AM

And ... if you start with the last used row in row B (B17 in your example), and run this macro, it will select successive cells to the right of B17 and keep running the macro until it finds and empty cell.

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 01-18-2013, 05:17 AM

Put this formula in 10th Row (Eg : Q10).

Please Login or Register  to view this content.

Copy and paste this For entire range at once. (Eg: Q11:Q1100)

Similarly for average also.

Please Login or Register  to view this content.

**mmchung** · 01-18-2013, 07:17 AM

Awesome!

NickyC, you rock! They worked perfectly. Thanks very much for your help!

kvsrinivasamurthy, also thanks for your answers, although what I need are VBA macros, so that I don’t have to copy and paste for 30 workbooks.