Adding rows by value location, excel crash

[Deverti]

This probably isnt the the most appropriate solution to begin with but it gets the job done, almost.

Thing is, "BufferBelow" works as intended while "BufferAbove" leads excel into a crash.

What s the big difference? i dont understand ~

Sub BufferBelow()
Application.ScreenUpdating = False
With ActiveSheet
    Dim vRb As Range
  
   For Each vRb In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Select Case vRb.Value
         Case "Aufwand", "Ertrag"
            vRb.Offset(0, 1).EntireRow.Insert
      End Select
   Next vRb
End With
Application.ScreenUpdating = True
End Sub

Sub BufferAbove()
Application.ScreenUpdating = False
With ActiveSheet
    Dim vRa As Range
  
   For Each vRa In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Select Case vRa.Value
         Case "Total Aufwand", "Gewinn", "Total Ertrag"
            vRa.Offset(0, -1).EntireRow.Insert
      End Select
   Next vRa
End With
Application.ScreenUpdating = True
End Sub

 

[MARK858]

You are offsetting by -1 column in the second code, there is no column to the left of column A so it errs.
I suspect that you meant to be offsetting by rows not columns (in both codes).

 

[Deverti]

oh duh, quickly added/removed some things for visibility beforehand and screwed that part up

was using the below and encountered the described crash. perhaps im still blind

Sub BufferBelow()
...
           vRb.Offset(1).EntireRow.Insert
...

Sub BufferAbove()
...
           vRa.Offset(-1).EntireRow.Insert
...

 

[MARK858]

If what you have just posted is crashing then you need to post the entire code and the error message as that code is offsetting by rows (other than the variables name is different).

 

[Deverti]

I simply uploaded an example to this temporary file host. And ofc, let me know if i should upload it some other way

/tmp/files - Temporary File Hosting

 

[MARK858]

Can't download a file as on my phone. Please post the full code as requested

 

[Deverti]

Oh i see. Not quite sure i follow about posting the entire code part since it hasnt changed but this is the Sub which is acting up.

Im not getting any error messages, it simply keeps running til it freezes.

Sub AddSubTitleBufferA()
Application.ScreenUpdating = False
With ActiveSheet
    Dim vRa As Range
  
   For Each vRa In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Select Case vRa.Value
         Case "Total Aufwand", "Gewinn", "Total Ertrag"
            vRa.Offset(-1).EntireRow.Insert
      End Select
   Next vRa
End With
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Do you have any of those words in A1?

Btw you can remove both the With/End With lines as you aren't using them in the code.

I also think that you will probably need to loop from bottom to top as you are inserting rows but won't be able to test that until I get back in (a couple of hours).

 

[Deverti]

Oha, wasnt aware about "With ActiveSheet" being superfluous. And yes those words all do exist in Column A, several times.

Thanks for the help so far.

 

[MARK858]

I asked about cell A1 specifically not the rest of the column , I'll explain about the With statement when I get back in. Anyway....

Try the code below but don't be surprised if errors as typed on my phone and obviously untested.

Sub AddSubTitleBufferA()
Dim x as Long
For x = Range("A" & Rows.Count).End(xlUp).Row to 2 Step - 1
       Select Case Cells(x, 1).Value
         Case "Total Aufwand", "Gewinn", "Total Ertrag"
            Cells(x, 1).Offset(-1).EntireRow.Insert
        End Select
Next 
End Sub