Help with if statement in VBA code!

[ktsmith21]

Can somebody help me figure out the code below? I'm trying to insert rows as part of a macro. I have the code inserting lines working, but I need it only to insert lines where the value in column B is greater than 1000.

    Dim ws As Worksheet
    Dim rowsn As Integer, count2 As Integer
    Dim line As Range
    Dim partners As Integer
    Set ws = ActiveWorkbook.Sheets("UNIT_PRICE_COMP")
        ws.Activate
    Dim i As Long
    partners = Worksheets("Settings").Range("C13").Value
    
        For i = Range("B" & Rows.count).End(xlUp).Row To 8 Step -1
            'If Cells(count, 2).Value > 1000 Then
                Rows(i).Resize(partners - 1).Insert
            'End If
        Next i

Currently, the code is producing this:

Finally, I need the value in column B of each inserted row to match the value of the cell being evaluated as part of the loop.

Thank you in advanve for any help!

 

[DanteAmor]

Try this

      For i = Range("B" & Rows.Count).End(xlUp).Row To 8 Step -1
        If Cells(i, 2).Value >= 1000 Then
            Rows(i + 1).Resize(partners - 1).Insert
        End If
      Next i

 

[ktsmith21]

Dante,
Worked great! Thank you. You even changed it to be >= to 1000, good catch there

Any idea on how to pull the value in column B into each of the inserted rows? For example, if I am inserting three rows under value 1000, I need each of those three rows to = 1000 also.

 

[DanteAmor]

I need each of those three rows to = 1000 also.

Try:

  For i = Range("B" & Rows.Count).End(xlUp).Row To 8 Step -1
    If Cells(i, 2).Value >= 1000 Then
      Rows(i + 1).Resize(partners - 1).Insert
      Cells(i + 1, 2).Resize(partners - 1).Value = Cells(i, 2).Value
    End If
  Next i

 

[ktsmith21]

This works perfectly, and is very concise. It's beautiful.

Thanks for your help, I really appreciate it.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.