Move onto next instance of IF statement

[teatimecrumpet]

Hi,

The below code works at finding a cell with a negative number. Copying the row and inserting it above the row.

However, It will continually find that row and add that same row instead of moving on to the next real instance of a negative number.


Does anyone know how to get out of the loop and move on the next qcell?

Thanks!

Sub insert_row_when_cell_neg()
Dim SR As Long
Dim qcell As Range
Dim col2 As Range
SR = Cells(Rows.Count, 21).End(xlUp).Row
Set col2 = Range("U2:U" & SR)
For Each qcell In col2

If qcell.Value < 0 Then

qcell.EntireRow.Copy
qcell.Offset(0, -20).Insert (xlShiftDown)
End If

Next
End Sub

 

[HalfAce]

Whenever looping through and adding or deleting rows, we need to loop up from the bottom instead of from the top down, for this very reason.
Once a loop looks at row 2, determines it needs to be (for example) deleted, then it looks at row 4, because what used to be row 3 is now row 2, and that's already been looked at so in reality row 3 will get ignored. (make sense?)

Anyway, does this do what you're intending to do?

Sub insert_row_when_cell_neg()
Dim SR As Long
Dim qcell As Long
Dim col2 As Range
SR = Cells(Rows.Count, 21).End(xlUp).Row
Set col2 = Range("U2:U" & SR)
Application.ScreenUpdating = False

For qcell = SR To 2 Step -1
  If Cells(qcell, "U").Value < 0 Then
    Rows(qcell).EntireRow.Copy
    Cells(qcell, "U").EntireRow.Insert (xlShiftDown)
  End If
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Hope it helps. (Let us know if I missed.)

 

[teatimecrumpet]

Thanks FullAce,

It does make sense.


To modify the code I got rid of the "col2" references.

Just for clarification " 2 step -1" means go back 1 up to row 2?

Here is my below working code for anyone else needs it.

It will find a negative number in a column copy the row and then insert it. THEN it will copy the qcell and paste it into the adjacent cell in the preceding column.

The reason I neededd this is because a purchase and a sale were showing up in one row but I needed this to be split out as two separate entries (rows).

thanks!

Dim SR As Long
Dim qcell As Long

SR = Cells(Rows.Count, 21).End(xlUp).Row

For qcell = SR To 2 Step -1

If Cells(qcell, "U").Value < 0 Then
Rows(qcell).EntireRow.Copy
Cells(qcell, "U").EntireRow.Insert (xlShiftDown)

Cells(qcell, "U").Copy Cells(qcell, "U").Offset(0, -1)
End If

Next

 

[HalfAce]

Yes, your instincts are serving you well grasshoppa. ( )
The line:
For qcell = SR To 2 Step -1
is simply telling it to go from the variable row (SR) to row 2, backwards (up instead of down), 1 row at a time.


(EDIT
Just noticed the 'FullAce' moniker.
At least it's not 'WholeAce' as some suggest. (And for which the guys here at work can never seem to get in the right order...)