LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 47
Default The For Loop Worked, but need critique

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




 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find loop doesn't loop JSnow Excel Discussion (Misc queries) 2 June 24th 09 08:28 PM
The reply worked, but I still need help.... John1791 Excel Worksheet Functions 5 December 27th 07 04:57 PM
Most hours worked spankydata New Users to Excel 3 August 10th 06 09:51 AM
It Worked!!! :-) thelees Excel Discussion (Misc queries) 1 September 25th 05 11:22 PM
Hours worked Keith Bowman Excel Worksheet Functions 2 November 26th 04 07:07 PM


All times are GMT +1. The time now is 12:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"