looping excel macros
 

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

looping excel macros
 

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/assistan...lconPT101.aspx

Brad wrote:

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

looping excel macros
 

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


On Mon, 4 Jan 2010 20:09:32 -0500, "Brad" wrote:

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

looping excel macros
 

Brad;602306 Wrote:
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

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

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 23
--------------------


or

Code:
--------------------
Do while j <15
'... your code including altering value of j here
Loop
--------------------


press f1 when your text cursor is sitting on the word 'do' in the vb
editor (or on the word 'for').


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166946

Microsoft Office Help

looping excel macros
 

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

"Brad" wrote:

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

.

looping excel macros
 

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" wrote:

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

.

looping excel macros
 

Worked great. Thanks everyone!

Brad

"Brad" wrote in message
...
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