Inserting one row

Question

Hi I have a problem when I try to insert one row under a selected one, here the code I'm using:

  For i = 1 To 150
  For k = 3 To 20

   If Cells(i, 6).value = Cells(k, 19).value Then

    Cells(i, 6).EntireRow.Select
    Selection.Insert Shift:=xlDown

  End If

 Next k
 Next i

I check if the value of Cells(I, 6) is equal of the value of the Cells(k, 19) and in that case I have to add one row under Cells(I, 6); the problem is that it works but it adds 20 rows instead of one, how can I fix?

@Nacorid yes but if I show a MsgBox it appears only the time the two cells have the same value — Francesco Bordignon, Jul 30 '19 at 13:40
Think about the logic here. Lets say it finds a row `Cells(i, 6)` when `i` is `4`. It inserts a row shifting your current row down. Then it goes to your NEXT row which is the same row you are on because you just shifted it down one. It adds another row and shifts your current row down. Then it goes to the NEXT row and it adds another row. It keeps hitting. It probably stops at 20 because at this point you've finally shifted `Cells(k, 19).value` down out of the range in which `k` is looping and it finds some relief from this endless loop. Rethink these steps. — JNevill, Jul 30 '19 at 13:41
@JNevill you are right, how can I prevent the shift down of the row? — Francesco Bordignon, Jul 30 '19 at 13:43
If you only anticipate a single row ever being inserted for a given `i` row, then do a `i=i+1` and then `exit for` to bump out of the `k` loop; though that will leave you not hitting all `150` of your `i` since they are being shifted down as you go. You might want a `while` or `until` loop instead that breaks when you hit a blank `cells(i,6)` insuring you test every row. The other issue is that your `cells(k,19)` will keep shifting down too as you add rows into its range. So maybe an entire sheet redesign is in order here where you store your `cells(k,19)` table in another sheet? — JNevill, Jul 30 '19 at 13:47
@JNevill I have solved in this way Cells(I+1, 6).EntireRow.Select Selection.Insert Shift:=xlUp — Francesco Bordignon, Jul 30 '19 at 13:49
Obligatory [How to avoid using Select in VBA](https://stackoverflow.com/a/10717999/8769365) — Nacorid, Jul 30 '19 at 13:51
@FrancescoBordignon That's a nice workaround. I think you are still going to get into the issue with your `k` loop and those values getting shifted out of your rows `3 to 20` range if you hit an `i` row in those same rows, but you are definitely headed in the right direction. — JNevill, Jul 30 '19 at 13:52

score 0 · Answer 1 · answered Jul 30 '19 at 14:27

Im using x as a flag here. Im also starting from the bottom because if you add a blank row then its just going to check that blank = blank and keep adding more blank rows

Dim i As Integer
Dim f As Boolean
Dim k As Integer

    Dim x As Boolean

    For i = 150 To 1 Step -1
      x = False
      For k = 20 To 3 Step -1
       If (Cells(i, 6).Value = Cells(k, 19).Value) And Not x Then

        Cells(i, 6).EntireRow.Insert Shift:=xlDown
        x = True

      End If

     Next k
     Next i

Inserting one row

1 Answers1