looping excel macros

[Brad]

I have Excel 2002 and routinely use the macro recorder. I can read the
macros and make simple edits, but never learned to program in VB and my
fortran programming experience is from 30 years ago. After recording a
macro, I want it to loop 15 times before exiting. What lines to I need to
add to the code to start and end the loop? I already have the macro. It
basically inserts a line, totals certain columns and formats one cell.

Brad

 

[Dave Peterson]

You could use a "for/next" loop

Dim dCtr as long
for dctr = 1 to 15
'do something
next dctr

But if you're inserting rows, it's usually much better to start at the bottom of
the range and insert there.

For instance....

Dim FirstRow as long
dim LastRow as long
dim iRow as long

with worksheets("sheet999")
firstrow = 2 'avoid row 1
'through the last used cell in column A.
lastrow = .cells(.rows.count,"A").end(xlup).row

for irow = lastrow to firstrow step -1
.rows(irow).insert
next irow
end with

======
All that said, have you looked at Data|Subtotals. (Sort your data first.)

It'll insert new rows and put subtotals (count, sum, average, min, max, ...) on
that new row.

=======
And if you're trying to get a count/sum of stuff, you may want to learn about
pivottables.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Gord Dibben]

Dim i as integer

For i = 1 to 15

do your stuff

next

For more help on required and optional arguments see VBA help on for next


Gord Dibben MS Excel MVP

 

[p45cal]

I have Excel 2002 and routinely use the macro recorder. I can read the
macros and make simple edits, but never learned to program in VB an
my
fortran programming experience is from 30 years ago. After recordin
a
macro, I want it to loop 15 times before exiting. What lines to I nee
to
add to the code to start and end the loop? I already have the macro.
It
basically inserts a line, totals certain columns and formats one cell.

Brad

Code
-------------------
For i = 1 to 15
'your repeating code here (or a call to your macro)
Next
-------------------

You can use i within your code, so the first line could be say:
For i = 16 to 30
or
For i = 10 to 50 step 3
or
For i = 20 to 5 step -1
etc. etc.
There are other loops:

Code
-------------------
Do
'... your code including altering value of j here
loop until j > 2
-------------------


or

Code

 

[RyanH]

Just put your code where I indicated and it will run 15 times then stop.
Hope this helps! If so, let me know, click "YES" below.

Sub RunMacro()

Dim TimesRan As Long

Do Until TimesRan = 15

' put your code here

TimesRan = TimesRan + 1

Loop

End Sub

 

[RyanH]

Just cut and paste your code where I indicated below. I hope this help! If
so, let me know, click "YES" below.

Sub RunMacro()

Dim TimesRan As Long

Do Until TimesRan = 15

' put your code here

TimesRan = TimesRan + 1

Loop

End Sub

 

[Brad]

Worked great. Thanks everyone!

Brad