Prev: summing cells in different sheets
Next: searching for a type of string
From: Brad on 4 Jan 2010 20:09
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

From: Dave Peterson on 4 Jan 2010 20:33
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

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
From: Gord Dibben on 4 Jan 2010 20:37
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" <bwyarger(a)hotmail.com> 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

From: p45cal on 4 Jan 2010 20:46

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/showthread.php?t=166946

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: RyanH on 4 Jan 2010 21:06
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
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: summing cells in different sheets
Next: searching for a type of string