Looping in Excel 2007 Macros

peternanderson

New Member
Joined
Dec 30, 2010
Messages
5
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
 

peternanderson

New Member
Joined
Dec 30, 2010
Messages
5
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

Legend
Joined
Jun 19, 2002
Messages
63,651
So perhaps something like

Code:
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

New Member
Joined
Dec 30, 2010
Messages
5
Thanks much.

I'll try that and let you know.

Peter
 

peternanderson

New Member
Joined
Dec 30, 2010
Messages
5
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
 

peternanderson

New Member
Joined
Dec 30, 2010
Messages
5
<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

Legend
Joined
Jun 19, 2002
Messages
63,651
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Please don't send PMs unless invited. You need to top and tail the code

Code:
Sub MyMacro
'
'Code goes here
'
End Sub
 

Forum statistics

Threads
1,082,261
Messages
5,364,106
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top