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

 

[Deverti]

Ok, now im very thankful for your help but also quite confused :x
A1 is empty and i did see your request but, with my lack of knowledge here i simply assumed it was a typo, my bad ~

If i use your code with Offset(0), or without it ofc, it does exactly what i need, which is awesome hehe.

however, i now tried it 3 times with Offset(-1) and during these attempts it
1: interacted with "Total Aufwand" as it did without offset; ignored "Gewinn"; added a row one row above where it does without offset on "Total Ertrag"
2: ignored "Total Aufwand"; interacted with "Gewinn" as it did without offset; ignored"Total Ertrag"
3: same as "2:"

so make with that what you will, just figured you might care to know. my current issue is solved, thanks a bunch^^

 

[MARK858]

No surprise at all, I was waiting for you to say it wasn't doing what you want with -1 offset as Insert (without an offset) already puts it in the line above the current row.

I just left it how you had it because you didn't explain in your descriptions what you wanted the code to actually do.

 

[Deverti]

i meant, confused because it seems that using Offset(-1) resulted in random interactions with the defined cases

ofc it s most likely that i somehow managed to screw with it without realizing having done so but as far as my perception goes i legit used the exact same code with varying returns

 

[MARK858]

Nothing random, it is because it is inserting a blank row 2 up and so throws off the count in the loop, you get the opposite effect when deleting rows from top to bottom.

 

[MARK858]

Example say you went to do your -1 insert on row 10 i.e. you start with

Book1
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
Sheet1

Then you do your -1 insert you get

Book1
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9
10	9
11	10
Sheet1

so what was originally row 9 is now row 10 and so will be skipped in the loop as it goes from the original row 10 to the new row 9.

 

[Deverti]

oh man, should ve realized that myself, didnt know the original count would serve as basis for everything while neglecting any additional content that would interfere with the count.
getting it done bottom up isnt a mystery anymore either, jesus. thanks for clearing that up

 

[MARK858]

You're welcome and happy you understand why now