Insert missing rows into repeating number sequence

Originally Posted by mikerickson

Set oneCell = Range("F1")

Do Until oneCell.Value = vbNullString
    With oneCell
  
        If (.Offset(1, 0).Value = 0 And (.Value <> 21 And .Value <> 22)) Or (.Offset(1, 0).Value = .Value + 1) Then
            Rem Ok line insert nothing
        Else
            .Offset(1,0).EntireRow.Insert shift:=xlDown
            .Offset(1,0).Value = .Value + 1
        End If
    End With
    Set oneCell = OneCell.Offset(1, 0)
Loop

Thanks for the reply mikerickson, seems to work great on my test data. I'll run it tomorrow on the full data set and let you know how it goes, but its looking perfect at this stage.
Thanks for your help.

Originally Posted by mikerickson

Here is an improvement that protects against wrong data and prevents an infinite loop.

Sub test()
    Dim oneCell As Range
    Set oneCell = Range("F1")
    
    Do Until CStr(oneCell.Offset(1, 0).Value) = vbNullString
        With oneCell
        
            If Not (IsNumeric(.Value)) Or Not (IsNumeric(.Offset(1, 0).Value)) Then
                MsgBox "fouled data"
                Exit Sub
                
            ElseIf (.Offset(1, 0).Value = 0 And (.Value <> 21 And .Value <> 22)) Or (.Offset(1, 0).Value = .Value + 1) Then
                Rem Ok line insert nothing
                
            ElseIf Val(.Offset(1, 0).Value) <= Val(.Value) Then
                MsgBox "fouled data"
                Exit Sub
                
            Else
                .Offset(1, 0).EntireRow.Insert shift:=xlDown
                .Offset(1, 0).Value = .Value + 1
            End If
            
        End With
        Set oneCell = oneCell.Offset(1, 0)
    Loop
End Sub

mikerickson I tested both codes you sent and as you suggested the first one did indeed get stuck in an infinite loop, the second did not so thanks very much for your help.

I noticed that the first code got stuck in infinite loop when encountering the error in column F of a missing 0. So for example 22,23,1 then the code would loop when it tried to insert a 0.
This is my fault as I originally stated that the pattern always restarts with a 0, I've lots of these data sets and very rarely the 0 is missed after 23. Is there anyway to modify the macro to allow for this and insert a 0 if its missing after a 23.

I've also noticed in one of my data sets that the very first line of data in F is actually a 1 not a 0. In this case I require a blank row inserted with a 0 before the 1.

I know I'm asking a lot here as i'm pretty new to code and i'm not great at it but i am attempting to tinker to get it right, however any assistance is greatly appreciated.

Again thanks for your time, your help has been great. I've again included a worksheet of raw data and expected outcome

Test2.xls

Originally Posted by mikerickson

Is there anyway to modify the macro to allow for this and insert a 0 if its missing after a 23.

so
19, 20, 21, 22, 23, 24, 4
would go to
19,20,21,22,23,24, 0, 1, 2, 3, 4

What would
8, 9, 10, 3 go to

The pattern is 0-23 so there wont ever be a 24 so the first example should read

19, 20, 21, 22, 23, 4
would go to
19,20,21,22,23, 0, 1, 2, 3, 4

technically the second example would be:
8, 9, 10, 3
would go to
8.9,10,0,1,2,3

mikerickson, that appears to have done the trick! At least with my home based testing anyway. Will thoroughly test tomorrow and let you know how I get on. Thank you so much for all of your help, your time and patience has been much appreciated and I feel I've learned a lot even from just tinkering with your code

mikerickson, I fully tested this today and it worked a treat! Thanks for all your help.