My first software programming cours at college taught me a lot of how to get
out of loops. The teacher had us write a program to manage student records.
Such as add students (told us to add 5 but only had four lines of input), add
grades, sort by courses. He also gave us an input file that had loads of
errors. You automaticallly got an A in the course if you were able to
completely process the entire input file without hanging up. I had to go
back and make many modifications to my code before out got out of all the
loops.
What added to the problem we were using a Main-Frame computer and writig the
program using punch cards. The program turned out to be a deck of 600 punch
cards. With the lines in the computer room it took at least a couple of
hours to get back the results from each run.
"Janis" wrote:
> debugging could be really tough without your help on this list. it is truly
> a wonderful resource. thanks,
>
> "Joel" wrote:
>
> > In your original code you should remove the else with no non-else branch.
> > You need tto changge = to <> and simply remove the else.
> >
> > the above makes it simpler to get out of the loop. I change the FOR lop to
> > increment one more time and then added an OR to the if statmentt to force it
> > into the code you want to execute
> >
> > Public Sub n2m4()
> >
> > Const ServiceGroupColumn As String = "$H"
> > Const FirstDataRow As Integer = 12
> >
> >
> > Dim iRow As Long
> > Dim rowsToAdd As Integer
> > Dim LastRow As Long
> > Dim counter As Integer
> > Dim rng As Range
> > Dim SvcGrpNum As Long
> > Dim SvcGrp As String
> >
> > SvcGrpNum = InputBox( _
> > "Please input the the total number of Service Group connections from the
> > DNP", _
> > "Service Group Number", 48)
> >
> >
> > With ActiveWorkbook.Worksheets("VOD")
> > LastRow = .Cells(.Rows.Count, ServiceGroupColumn).End(xlUp).Row
> > counter = 1
> >
> > For iRow = (LastRow + 1) To (FirstDataRow - 1) Step -1
> > Debug.Print " " & iRow & "irow"
> >
> > counter = i + 1
> > Debug.Print " " & " " & i & "i"
> >
> > If (.Cells(iRow, ServiceGroupColumn).Value <> _
> > .Cells(iRow - 1, ServiceGroupColumn).Value) Or _
> > (iRow = (FirstDataRow - 1)) Then
> >
> > rowsToAdd = SvcGrpNum - i
> > Set rng = .Cells(iRow, ServiceGroupColumn)
> > SvcGrp = rng.Offset(SvcGrpNum / 2, 0).Value
> > rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd +
> > 1).EntireRow.Insert
> > rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).Value = SvcGrp
> > counter = 1
> >
> > End If
> >
> >
> > Next iRow
> >
> > End With
> >
> > End Sub
> >
> >
> > "Janis" wrote:
> >
> > > If this macro gets to row 12, which is the last data row, I want it to exit
> > > the counter for loop and execute the else statement one last time. How do I
> > > get it to do that? If I'm on the last row I want it to add the rows to the
> > > last service group but it can't add any more rows becuase it is the end.
> > >
> > > tia,
> > >
> > >
> > >
> > > Public Sub n2m4()
> > >
> > > Const ServiceGroupColumn As String = "$H"
> > > Const FirstDataRow As Integer = 12
> > >
> > >
> > > Dim iRow As Long
> > > Dim rowsToAdd As Integer
> > > Dim LastRow As Long
> > > Dim counter As Integer
> > > Dim rng As Range
> > > Dim SvcGrpNum As Long
> > > Dim SvcGrp As String
> > >
> > > SvcGrpNum = InputBox("Please input the the total number of Service Group
> > > connections from the DNP", "Service Group Number", 48)
> > >
> > >
> > > With ActiveWorkbook.Worksheets("VOD")
> > > LastRow = .Cells(.Rows.count, ServiceGroupColumn).End(xlUp).Row
> > > counter = 1
> > >
> > > For iRow = (LastRow + 1) To (FirstDataRow) Step -1
> > > Debug.Print " " & iRow & "irow"
> > >
> > > counter = i + 1
> > > Debug.Print " " & " " & i & "i"
> > >
> > > If .Cells(iRow, ServiceGroupColumn).Value = .Cells(iRow - 1,
> > > ServiceGroupColumn).Value Then
> > >
> > > Else
> > >
> > >
> > >
> > >
> > > rowsToAdd = SvcGrpNum - i
> > > Set rng = .Cells(iRow, ServiceGroupColumn)
> > > SvcGrp = rng.Offset(SvcGrpNum / 2, 0).Value
> > > rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).EntireRow.Insert
> > > rng.Offset(SvcGrpNum / 2, 0).Resize(rowsToAdd + 1).Value = SvcGrp
> > > counter = 1
> > >
> > > End If
> > > '
> > >
> > >
> > >
> > >
> > > Next iRow
> > >
> > > End With
> > >
> > > End Sub
> > >
|
Similar Threads
