Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
built myself into a corner, how to exit gracefully
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
built myself into a corner, how to exit gracefully
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
built myself into a corner, how to exit gracefully
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My red "X" button (exit) inthe top right corner of excel is gone | Excel Discussion (Misc queries) | |||
insert 2 colours in a single cell,corner to corner | Excel Worksheet Functions | |||
Looking for Rod over in the corner... | Excel Programming | |||
Quiting Gracefully! | Excel Programming | |||
If a called sub exit, how to the caller exit right away? | Excel Programming |