built myself into a corner, how to exit gracefully
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
|