Looping in Excel 2007 Macros

[peternanderson]

I need to set up a subroutine to make various calculations monthly whose totals then are placed in each year's row.

It appears that Excel 2007 requires Visual Basic to do that, although if there is a simpler way to do that, please let me know that too.

I set up a counter, so I believe that writing the following three lines in VB should get me where I want to go:

<table style="border-collapse: collapse; width: 513pt;" width="682" border="0" cellpadding="0" cellspacing="0"><col style="width: 85pt;" width="113"> <col style="width: 88pt;" width="117"> <col style="width: 85pt;" width="113" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 85pt;" width="113" height="20"> 1 </td> <td class="xl65" colspan="5" style="width: 428pt;" width="569"> If COUNTER>20 and COUNTER<120, Copy Cell Q45 to Relative Cell E [COUNTER] </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> 2 </td> <td class="xl65" colspan="5" style=""> If COUNTER>20 and COUNTER<120, Copy Cell P44 to Relative Cell D [COUNTER] </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20"> 3 </td> <td class="xl65" colspan="2" style=""> Increment the Counter by 1 </td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> </tbody></table>Any help would be appreciated.

Peter

 

[VoG]

Can you explain in words what you want to do.

 

[peternanderson]

The main spread sheet records in rows for each year the values for the cumulative impact of monthly transactions handled off to the side in a subroutine. (Because of compounding effects, and the need to be more precise, simply performing the year-end calculation would not be sufficiently accurate.

So I have set up a line by line subroutine for these compounding calculations each month that go to a summation line for each successive year.

I need then move the answer for the cumulative results for each year to the appropriate line (i.e. the incrementing year's row), ratchet the counter by 1, and then repeat the process for the next relative row of year's values.

Hoping this makes sense.

 

[VoG]

So perhaps something like

For COUNTER = 21 To 119
    Range("Q" & COUNTER + 24).Copy Destination:=Range("E" & COUNTER)
    Range("P" & COUNTER + 23).Copy Destination:=Range("D" & COUNTER)
Next COUNTER

 

[peternanderson]

Thanks much.

I'll try that and let you know.

Peter

 

[peternanderson]

Dear VoG-

I'm trying to track what your code will do to insure that the totoal for each month's subroutine data, which is always in a fixed cell, is copied to a relative cell with a fixed column but incrementing row.

What you gave me is:

For COUNTER = 21 To 119
Range("Q" & COUNTER + 24).Copy Destination:=Range("E" & COUNTER)
Range("P" & COUNTER + 23).Copy Destination:=Range("D" & COUNTER)
Next COUNTER</pre>
If I understand this correctly, however, the sequence takes a relative cell (Q+COUNTER) and moves it to relative cell (E+COUNTER). Shouldn't the cell being copied be fixed at the constant Q45 cell where it is found?

Thanks so much.

Peter

 

[VoG]

I'm not sure because I don't understand exactly what you want to do. Try posting small examples of what you expect before and after the macro. You can use Excel Jeanie to do that http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[peternanderson]

<table style="border-collapse: collapse; width: 428pt;" width="569" border="0" cellpadding="0" cellspacing="0"><col style="width: 85pt;" width="113"> <col style="width: 88pt;" width="117"> <col style="width: 85pt;" width="113" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td colspan="5" class="xl70" style="height: 15pt; width: 428pt;" width="569" height="20">
(1) The fixed location of each monthly iteration (actually here it is a decimal), from running each tenth of the year, and then sums the total for of the ten tenths for that iteration of the year in the bottom right hand cell, which is "Q45".

[FIXED SECTION OF SPREADSHEET WHERE ANNUAL DECIMAL ITERATION IS SHOWN]

SUCCEEDING YEARS OF DECIMAL ITERATION </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Decimal</td> <td class="xl69">Cell Reference</td> <td class="xl66">Mg Bgnning of Tenth</td> <td class="xl66">Mg End of Tenth</td> <td class="xl66">m3/year</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.1 </td> <td>C21</td> <td class="xl65"> 10,961 </td> <td class="xl65"> 10,917 </td> <td class="xl65"> 7,394 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.2 </td> <td>
</td> <td class="xl67"> 10,917 </td> <td class="xl65"> 10,873 </td> <td class="xl65"> 7,364 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.3 </td> <td>
</td> <td class="xl67"> 10,873 </td> <td class="xl65"> 10,830 </td> <td class="xl65"> 7,335 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.4 </td> <td>
</td> <td class="xl67"> 10,830 </td> <td class="xl65"> 10,786 </td> <td class="xl65"> 7,305 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.5 </td> <td>
</td> <td class="xl67"> 10,786 </td> <td class="xl65"> 10,743 </td> <td class="xl65"> 7,276 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.6 </td> <td>
</td> <td class="xl67"> 10,743 </td> <td class="xl65"> 10,700 </td> <td class="xl65"> 7,247 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.7 </td> <td>
</td> <td class="xl67"> 10,700 </td> <td class="xl65"> 10,657 </td> <td class="xl65"> 7,218 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.8 </td> <td>
</td> <td class="xl67"> 10,657 </td> <td class="xl65"> 10,615 </td> <td class="xl65"> 7,189 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">0.9 </td> <td>
</td> <td class="xl67"> 10,615 </td> <td class="xl65"> 10,572 </td> <td class="xl65"> 7,161 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">1.0 </td> <td>
</td> <td class="xl67"> 10,572 </td> <td class="xl65"> 10,530 </td> <td class="xl65"> 7,132 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">
</td> <td>
</td> <td class="xl65">
</td> <td class="xl65"> Total for Year= </td> <td class="xl71"> 72,622 </td> </tr> </tbody></table>

(2) Then on the next step for each iteration, I need to copy the value 72,622 from fixed cell Q45 to the relative cell (although always in col. E, the row steps down each year to the next row). So for the first year, Q45 would be copied to E21 (the row the first year appears), but as the counter ratchets up, the next copy would be to E21+1 or E22, and so forth

So I'm assuming that the second line of your code should be changed to:

Q45. Copy Destination:=Range("E" & COUNTER)

Is that right?

 

[VoG]

Yes I believe that you are correct. Be sure to try this with a copy of your worksheet - macros are not reversible (there is no undo).

 

[VoG]

Please don't send PMs unless invited. You need to top and tail the code

Sub MyMacro
'
'Code goes here
'
End Sub