0

I have a worksheet named "subtotal" where I am identifying 2 line to be subtracted with 1 & 2 (line with "2" should be subtracted from line with "1"). I need to insert a row below the line with "2" in order to add the correct formula in the required cells. I am selecting the column containing the criteria (1,2), then executing a find comand for the "2", I then use the offset property to select the cell below and insert a row. This works great, however when I apply the loop it will not stop. I have indicated that if the active cell = "2XXXXX" then exit Do. I have tried several variations and end up with the same endless loop. Can anyone tell me what i am doing wrong? Here is my code:

  Sub insert_row_1()
    '
     Range("D1").Select
        Selection.End(xlDown).Activate
        ActiveCell.Offset(1, 0).Activate
        ActiveCell.FormulaR1C1 = "2XXXXX"
    Range("A1").Select

        Columns("D:D").Select
       Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate


     Do
      If ActiveCell.Value = "2" Then
       ActiveCell.Offset(1, 0).Activate
        ActiveCell.EntireRow(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

      ElseIf ActiveCell.Value <> "2" Then

       With Columns("D")

       Selection.FindNext(After:=ActiveCell).Activate
        ActiveCell.Offset(1, 0).Activate
        ActiveCell.EntireRow(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        End With


    ElseIf ActiveCell.Value = "2XXXXX" Then
    Exit Do
    End If



    Loop


    End Sub
Community
  • 1
  • 1
user3689031
  • 5
  • 2
  • 4
  • 1
    Most likely, every problem or potential problem you have can be resolved if you adhere to best practices: [Avoid using Select or Activate in VBA](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – David Zemens Jun 24 '14 at 16:11

1 Answers1

1

Unfortunately, I was not able to get your loop to work, but, if I understand your goal correctly, there may be an easier way to accomplish what you need.

As I understand it, you want to:

  1. Insert a row below every row that has a 2 in Column D
  2. In column E of the new row, subtract the value of Column E one row above the new row from the value in column E one row above that.

The below will accomplish that:

Sub InsertAfter2()
For Each Cell In Range("D:D")
If Cell.Row <> 1 Then
If (Cells(Cell.Row - 1, 4).Value = 2) Then
Cell.EntireRow(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(Cell.Row - 1, 4).Value = "Result:"
Cells(Cell.Row - 1, 5).Value = (Cells(Cell.Row - 3, 5).Value) - (Cells(Cell.Row - 2, 5).Value)
End If
End If
Next Cell
End Sub

This will take data that looks like:

enter image description here

And produce data that looks like:

enter image description here

Liza
  • 501
  • 3
  • 9