Hi

it's a long post but one thing that is always preferable is to address your
objects directly rather than by selecting them e.g.

untested, mind
Range("B2").copy
rather than
Range("B2").select
selection.copy

it is shorter to code and MUCH quicker to run

once your used to it, it is possibly easier to debug.

Regards

Tim


"Bruce Roberson" wrote in message
...
It took me a day and a half, but I wrote my first "For
loop", and it does what I want it to. I am posting this to
invite critique to see how it could be more efficiently
written or cleaned up some if any.

The purpose of this loop was to work with Columns D and E,
which are page and line number columns respectively. They
are text entries when I get through with them even though
I am using a numeric value pagenumcounter and
linenumcounter to keep up with the values as they change.
But page 1 is shown in the field as 01, and line 1 is
shown as 01, so that they sort properly with page 10 and
line 10 values etc when they are in a database.

There are several constraints placed on the pagenum. Each
time, the range linenum gets to 12, then the next time the
loop processes the line, the pagenum is to go up by 1, and
the linenum is to go back to a 1, and start climbing
again. The other constraint is that if the value in column
B changes which is prmo, then we want to treat the page
and line number the same as if the line number gets to 12.
We want to start the line number back at 1 and add 1 to
the page number regardless of what the line number had
been. So, I compare two spreadsheet range
values "Currprmo" and "prevprmo" in the spreadsheet to
accomplish that part. I used spreadsheet range "currprmo"
to manage the current line being processed, and I used the
spreadsheet range "prevprmo" to look back at what the
value of prmo was in the previous line.

With all that said, here is my procedure for your critique:

Sub add_page_and_line()
Dim cRows As Long
cRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("pagenumcounter").Value = 1
Range("linenumcounter").Value = 0
Range("rownow").Value = 1
Range("d1").Select
For i = 1 To cRows
Range("Currprmo").Value = Range("B1").Offset(Range
("Rownow") - 1, 0).Value
If i = 1 Then
Range("Prevprmo").Value = Range("B1").Value
Else
Range("Prevprmo").Value = Range("B1").Offset(Range
("Rownow") - 2, 0).Value
End If
Range("rownow").Value = Range("rownow").Value + 1
If Range("Linenumcounter").Value + 1 = 13 Or Range
("Currprmo").Value _
< Range("Prevprmo") Then
Range("pagenumcounter").Value = Range
("pagenumcounter").Value + 1
Range("linenumcounter").Value = 1
Else
Range("linenumcounter").Value = Range
("linenumcounter").Value + 1
End If
ActiveCell.Value = "=pagenum"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=linenum"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, -1).Select
Next
Range("d1").Select
End Sub