for loop doesn't advance

[Janis]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Janis]

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 here:

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