Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
The reply worked, but I still need help.... | Excel Worksheet Functions | |||
Most hours worked | New Users to Excel | |||
It Worked!!! :-) | Excel Discussion (Misc queries) | |||
Hours worked | Excel Worksheet Functions |