Addition of rows in table with loop
 

I have the following code to help me insert a row in between groups o
similar data.
However, it doesn't work after the first row has been inserted. Coul
someone tell me what's wrong with my code?

For Each c In ActiveWorkbook.Worksheets("CIT
GF").Range(myrange.Address)

If Cells(c.Row, 9).Value < Cells(c.Row + 1, 9).Value Then
'Address = c.Address
c.EntireRow.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
'Range("c.Row,c.column").Offset(1, 0).Select
c = c.Offset(3, 0)
End If
Next c


Regard

--
Message posted from http://www.ExcelForum.com

Addition of rows in table with loop
 

Desmond,

Change

c = c.Offset(3, 0)

To

Set c = c.Offset(3, 0)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"desmondleow " wrote
in message ...
I have the following code to help me insert a row in between
groups of
similar data.
However, it doesn't work after the first row has been inserted.
Could
someone tell me what's wrong with my code?

For Each c In ActiveWorkbook.Worksheets("CITI
GF").Range(myrange.Address)

If Cells(c.Row, 9).Value < Cells(c.Row + 1, 9).Value Then
'Address = c.Address
c.EntireRow.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
'Range("c.Row,c.column").Offset(1, 0).Select
c = c.Offset(3, 0)
End If
Next c


Regards


---
Message posted from http://www.ExcelForum.com/

Addition of rows in table with loop
 

Hi...

It still doesn't work. It keeps inserting new rows and doesn't move
down to the next line of data. Is there any reason why?

Regards,
Desmond


---
Message posted from http://www.ExcelForum.com/

Addition of rows in table with loop
 

The problem is that when you insert the row you change the are being
processed, and the inserted row becomes part of the next test. This never
matches, so you insert another row at the same point, and so on, until you
get an error.

What you need to do is work from the bottom up, like so

With ActiveWorkbook.Worksheets("CITIGF").Range(myrange)
For i = Cells(Rows.Count, .Column).End(xlUp).Row To .Row + 1 Step -1
If Cells(i, 9).Value < Cells(i - 1, 9).Value Then
'Address = c.Address
Cells(i, 1).EntireRow.Insert Shift:=xlDown
End If
Next i
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"desmondleow " wrote in message
...
I have the following code to help me insert a row in between groups of
similar data.
However, it doesn't work after the first row has been inserted. Could
someone tell me what's wrong with my code?

For Each c In ActiveWorkbook.Worksheets("CITI
GF").Range(myrange.Address)

If Cells(c.Row, 9).Value < Cells(c.Row + 1, 9).Value Then
'Address = c.Address
c.EntireRow.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
'Range("c.Row,c.column").Offset(1, 0).Select
c = c.Offset(3, 0)
End If
Next c


Regards


---
Message posted from http://www.ExcelForum.com/