Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop doesn't advance
Program is not advancing to the next Service Group. It keeps adding sets of
20 rows. It is starting on the 7th row and adding row after row in the same place. It should start on the 8th row and add 20 rows and then advance. Exactly what I want it to do is compare the value in cells in column H (starting with SG01 then before it gets to the next one , store the count of rows, for the 1st service group, which in most cases is 16. Then it has to divide that count in half and add 20 rows to the first half and 20 rows to the second half on to the end of the sheet. There are many sheets to do this on and the number of service groups vary as do the row count. Then it has to advance to the next service group which is where it is failing. It isn't advancing. tia, Public Sub n2m_3() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim UsedRange1 As Range Dim Rows() As Variant Dim i As Long Dim UsedCol1 As Long Dim C As Range Dim Second_QAM_IP As Integer Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" & ServiceGroupColumn & ActiveSheet.UsedRange.Rows.Count), ActiveSheet.UsedRange) UsedRange1.Select i = 0 For Each C In UsedRange1 If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then ActiveCell.Offset(1, 0).Activate i = i + 1 Else Second_QAM_IP = Round(i / 2) ActiveCell.Offset(-Second_QAM_IP, 0).Resize(20).EntireRow.Insert ActiveCell.Offset(Second_QAM_IP, 0).Resize(20).EntireRow.Insert i = 1 End If Next C End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop doesn't advance
Every place you have the reference to ActiveCell inside your loop, change it
C (what you called your variable in the For Each statement)... the cells being assigned to C in the For Each loop are not made active on the spreadsheet, they are directly accessible from the code without physically activating the cell. Rick "Janis" wrote in message ... Program is not advancing to the next Service Group. It keeps adding sets of 20 rows. It is starting on the 7th row and adding row after row in the same place. It should start on the 8th row and add 20 rows and then advance. Exactly what I want it to do is compare the value in cells in column H (starting with SG01 then before it gets to the next one , store the count of rows, for the 1st service group, which in most cases is 16. Then it has to divide that count in half and add 20 rows to the first half and 20 rows to the second half on to the end of the sheet. There are many sheets to do this on and the number of service groups vary as do the row count. Then it has to advance to the next service group which is where it is failing. It isn't advancing. tia, Public Sub n2m_3() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim UsedRange1 As Range Dim Rows() As Variant Dim i As Long Dim UsedCol1 As Long Dim C As Range Dim Second_QAM_IP As Integer Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" & ServiceGroupColumn & ActiveSheet.UsedRange.Rows.Count), ActiveSheet.UsedRange) UsedRange1.Select i = 0 For Each C In UsedRange1 If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then ActiveCell.Offset(1, 0).Activate i = i + 1 Else Second_QAM_IP = Round(i / 2) ActiveCell.Offset(-Second_QAM_IP, 0).Resize(20).EntireRow.Insert ActiveCell.Offset(Second_QAM_IP, 0).Resize(20).EntireRow.Insert i = 1 End If Next C End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop doesn't advance
thank you, at least now I can see what is going on, however, it still doesn't
work. The problem is since it is going down from the top it is adding blank rows and the service group column no longer is a key to match where the next service group starts and ends. How can I start this from the bottom and go up? please help, tia, ---------------second iteration---------------- Public Sub n2m_3() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim UsedRange1 As Range Dim Rows() As Variant Dim i As Long Dim UsedCol1 As Long Dim C As Range Dim count As Integer Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" & ServiceGroupColumn & ActiveSheet.UsedRange.Rows.count), ActiveSheet.UsedRange) UsedRange1.Select For Each C In UsedRange1 If C.Value = ActiveCell.Offset(1, 0).Value Then C.Offset(1, 0).Activate Else i = i / 2 C.Offset(-i + 1, 0).Resize(20).EntireRow.Insert C.Offset(i + 1, 0).Resize(20).EntireRow.Insert End If i = i + 1 Next C End Sub "Rick Rothstein (MVP - VB)" wrote: Every place you have the reference to ActiveCell inside your loop, change it C (what you called your variable in the For Each statement)... the cells being assigned to C in the For Each loop are not made active on the spreadsheet, they are directly accessible from the code without physically activating the cell. Rick "Janis" wrote in message ... Program is not advancing to the next Service Group. It keeps adding sets of 20 rows. It is starting on the 7th row and adding row after row in the same place. It should start on the 8th row and add 20 rows and then advance. Exactly what I want it to do is compare the value in cells in column H (starting with SG01 then before it gets to the next one , store the count of rows, for the 1st service group, which in most cases is 16. Then it has to divide that count in half and add 20 rows to the first half and 20 rows to the second half on to the end of the sheet. There are many sheets to do this on and the number of service groups vary as do the row count. Then it has to advance to the next service group which is where it is failing. It isn't advancing. tia, Public Sub n2m_3() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim UsedRange1 As Range Dim Rows() As Variant Dim i As Long Dim UsedCol1 As Long Dim C As Range Dim Second_QAM_IP As Integer Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" & ServiceGroupColumn & ActiveSheet.UsedRange.Rows.Count), ActiveSheet.UsedRange) UsedRange1.Select i = 0 For Each C In UsedRange1 If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then ActiveCell.Offset(1, 0).Activate i = i + 1 Else Second_QAM_IP = Round(i / 2) ActiveCell.Offset(-Second_QAM_IP, 0).Resize(20).EntireRow.Insert ActiveCell.Offset(Second_QAM_IP, 0).Resize(20).EntireRow.Insert i = 1 End If Next C End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
for loop doesn't advance
this version is a little better. It is adding 40 blank rows in the middle of
the service group for i instead of adding only 20. It should add the other 20 rows at the end of the service group. I don't know how to get back to the end of the first group and add the 20 rows he C.Offset(20 + (ix2) + 1, 0).Resize(20).EntireRow.Insert tia, -----------------code------------ Public Sub n2m_3() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim UsedRange1 As Range Dim Rows() As Variant Dim i As Long Dim UsedCol1 As Long Dim C As Range Dim count As Integer Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" & ServiceGroupColumn & ActiveSheet.UsedRange.Rows.count), ActiveSheet.UsedRange) UsedRange1.Select For Each C In UsedRange1 If C.Value = ActiveCell.Offset(1, 0).Value Then C.Offset(1, 0).Activate Else i = i / 2 C.Offset(-i + 1, 0).Resize(20).EntireRow.Insert C.Offset(20 + (ix2) + 1, 0).Resize(20).EntireRow.Insert End If i = i + 1 Next C End Sub "Rick Rothstein (MVP - VB)" wrote: Every place you have the reference to ActiveCell inside your loop, change it C (what you called your variable in the For Each statement)... the cells being assigned to C in the For Each loop are not made active on the spreadsheet, they are directly accessible from the code without physically activating the cell. Rick "Janis" wrote in message ... Program is not advancing to the next Service Group. It keeps adding sets of 20 rows. It is starting on the 7th row and adding row after row in the same place. It should start on the 8th row and add 20 rows and then advance. Exactly what I want it to do is compare the value in cells in column H (starting with SG01 then before it gets to the next one , store the count of rows, for the 1st service group, which in most cases is 16. Then it has to divide that count in half and add 20 rows to the first half and 20 rows to the second half on to the end of the sheet. There are many sheets to do this on and the number of service groups vary as do the row count. Then it has to advance to the next service group which is where it is failing. It isn't advancing. tia, Public Sub n2m_3() Const ServiceGroupColumn As String = "$H" Const FirstDataRow As Integer = 12 Dim UsedRange1 As Range Dim Rows() As Variant Dim i As Long Dim UsedCol1 As Long Dim C As Range Dim Second_QAM_IP As Integer Set UsedRange1 = Intersect(Range(ServiceGroupColumn & FirstDataRow & ":" & ServiceGroupColumn & ActiveSheet.UsedRange.Rows.Count), ActiveSheet.UsedRange) UsedRange1.Select i = 0 For Each C In UsedRange1 If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then ActiveCell.Offset(1, 0).Activate i = i + 1 Else Second_QAM_IP = Round(i / 2) ActiveCell.Offset(-Second_QAM_IP, 0).Resize(20).EntireRow.Insert ActiveCell.Offset(Second_QAM_IP, 0).Resize(20).EntireRow.Insert i = 1 End If Next C End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
How to Advance a For Next Loop prior to the Next Statement | Excel Programming | |||
I need a little help...thanks in advance | Excel Discussion (Misc queries) | |||
Here's one for you...(thanks for the help in advance) | Excel Discussion (Misc queries) | |||
Boolean to advance loop | Excel Programming |