Nested if statements / comparing two adjacent cells and inserting row
 

Hello,

Well at this point I have just under 500 lines of code and I am stuck on this
one little part. I have the majority of my macro set up to run off of
specific cells in multiple worksheets and I need to make sure that the
worksheets are formated the same to extract the necessary data.

In column A there are two instances of when I need to insert a blank row.
1. When "ES 2" is Followed by "PP 1"
2. When "PP 4" is Followed by "Totals"
** Because the individuals who invented this worksheet didn't think about
data migration the "PP" is sometimes neglected and it is just the numeric
value

Additionally, these values will appear in the same worksheet and workbook
multiple and varying times - so I think I need to use a loop or an interger
to achieve everything I need.

Please take a look at the code below and any help will be greatly appreciated.


Sub my_headache()

Dim rng As Range
Dim i As Integer
i = 1
Dim r As Integer
r = 1

rng = Columns("A:A")
For Each cell In rng
If (Right(Cells.Name(i, 1), 1)) = "2" Then
rng.Offset(1, 0)
If (Right(Cells.Name(r, 2), 1)) = "1" Then
Rows(i + 1).EntireRow.Insert
End If
End If
Next i

End Sub



Thanks,
Reign

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1

Nested if statements / comparing two adjacent cells and inserting row
 

When you are inserting rows(or columns) in a range you really want to
iterate through it starting from the end and working to the beginning.
This is because if you insert a row it adds one more row to the sheet
but as far as the range is concerned it is still the original size(the
size that you set it in the code) so at the end you'll have some of
your information out of the scope of the range and it won't be subject
to the If then statement.

Try using this format:

Sub Your_Advil()
On Error Resume Next
Dim rng As Range, mCell As Range
Dim i As Integer
Dim r As Integer
r = 1
Set rng = Range(Cells(Rows.Count, "A").End(xlUp), Cells(1, 1))
For i = rng.Rows.Count To 1 Step -1
If IsError(Cells(i - 1, 1)) Then Exit For
If Right(Cells(i, 1), 1) = "1" And _
Right(Cells(i - 1, 1), 1) = "2" Then
Rows(i + 1).EntireRow.Insert
End If
Next i
End Sub

HTH

Sandy


reign777 via OfficeKB.com wrote:
Hello,

Well at this point I have just under 500 lines of code and I am stuck on this
one little part. I have the majority of my macro set up to run off of
specific cells in multiple worksheets and I need to make sure that the
worksheets are formated the same to extract the necessary data.

In column A there are two instances of when I need to insert a blank row.
1. When "ES 2" is Followed by "PP 1"
2. When "PP 4" is Followed by "Totals"
** Because the individuals who invented this worksheet didn't think about
data migration the "PP" is sometimes neglected and it is just the numeric
value

Additionally, these values will appear in the same worksheet and workbook
multiple and varying times - so I think I need to use a loop or an interger
to achieve everything I need.

Please take a look at the code below and any help will be greatly appreciated.


Sub my_headache()

Dim rng As Range
Dim i As Integer
i = 1
Dim r As Integer
r = 1

rng = Columns("A:A")
For Each cell In rng
If (Right(Cells.Name(i, 1), 1)) = "2" Then
rng.Offset(1, 0)
If (Right(Cells.Name(r, 2), 1)) = "1" Then
Rows(i + 1).EntireRow.Insert
End If
End If
Next i

End Sub



Thanks,
Reign

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1