Macro stops too early - Help!

**cossie2k** · 06-25-2012, 06:44 AM

Hi Everyone,

I have a macro that inserts a new line under certain conditions (where "RAN" is shown)

The problem I have is that it works perfectly for the first 46 rows but will not work thereafter.

Can anyone help and advise where the issue is?

Thanks

Heres the code:-

Option Explicit

Sub new_inspection()
Dim lrow As Long, i As Long

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("W" & i).Value = "RAN" Then
            .Rows(i + 1).Insert
            .Range("A" & i & ":R" & i).Copy .Range("A" & i + 1)
            .Range("R" & i + 1).Value = "7G"
            .Range("W" & i + 1).Value = "RAN"
            i = i + 1
            lrow = lrow + 1000
        End If
    Next i
End With

End Sub

**jaslake** · 06-25-2012, 08:01 AM

Hi cossie2k
Look at this link, post #2 and pay particular attention to this comment

The trick with this problem is to have the code start at the last row and work up so that the row re-numbering due to the row insertion does not mess up the macro operation.

http://www.mrexcel.com/forum/showthr...-condition-met

**cossie2k** · 06-25-2012, 08:22 AM

Thanks John,

Reading over the link you attached, I have two questions:-

I though my original code (which has been doctored from this site!) was working from the end, up

    lrow = .Range("A" & .Rows.Count).End(xlUp).Row

The other thing is that Im not sure how to bend the formula on the link

Sub InsertRows()
Dim iRow As Long
Dim rCount As Integer 'running count of inserted rows
Dim iCount As Integer 'number of rows to insert
Dim i As Integer
Dim Bval As Variant 'Col B cell value to be written to new lines
rCount = 0
For iRow = [a65536].End(xlUp).Row To 2 Step -1
If Cells(iRow, 5) = 1 Then
iCount = Cells(iRow, 1) - Cells(iRow - 1, 1) - 1
Bval = Cells(iRow, 2)
For i = 1 To iCount
rCount = rCount + 1
Rows(iRow).Insert
Cells(iRow, 2) = Bval
Next i
End If
Next iRow
Beep
MsgBox rCount & " rows inserted", vbInformation, "Results"
End Sub

around what Im after. I dont understand what the component part are and how my requirements (based on my code) interact with these. I have tried

Sub insertrows()

Dim iRow As Long
Dim rCount As Integer 'running count of inserted rows
Dim iCount As Integer 'number of rows to insert
Dim i As Integer
Dim Bval As Variant 'Col B cell value to be written to new lines
rCount = 0

For iRow = [a65536].End(xlUp).Row To 2 Step -1
        If Cells(iRow, "W") = "RAN" Then
            iCount = Cells(iRow, 1) - Cells(iRow - 1, 1) - 1
            Bval = Cells(iRow, 2)
            For i = 1 To iCount
            rCount = rCount + 1
            Rows(iRow).Insert
            Cells(iRow, 2) = Bval
            
Next i

        End If

    Next iRow

End Sub

But it fails at

iCount = Cells(iRow, 1) - Cells(iRow - 1, 1) - 1

Also how do I get the "7G" bit in?

Thanks again
Alex

**jaslake** · 06-25-2012, 10:15 AM

Hi cossie2k

Your original code was working down the rows. Step-1 is what causes the code to work from the bottom up. Glad you got it working.

**cossie2k** · 06-25-2012, 08:34 AM

Hi John,

Ive fixed my original code.

I went down to line 2000 (far more than I will be using) on entered data across that row. Dont know why its work but it has!