built myself into a corner, how to exit gracefully

[Janis]

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

 

[Joel]

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]

debugging could be really tough without your help on this list. it is truly
a wonderful resource. thanks,

 

[Joel]

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.