Sequential numbering in Excel VBA randomly skips orders

Question

I try to create a sequential numbering method for a list based on any cell change

I've tried to change the For-Next with Do-While and Do-Until Loops without any success

Code

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim differnettypes As Integer
    Dim sequentialnumbering As Range
    Dim sequentialnumber As Integer

    If Not Intersect(Target, Range("B4:B30")) Is Nothing Then

        sequentialnumber = 1
        differenttypes = (Cells(Rows.Count, 3).End(xlUp).Row) - 5
        Set sequentialnumbering = Worksheets("Input").Range("B4:B30")
        For sequentialnumber = 1 To differenttypes
            sequentialnumbering.Cells(sequentialnumber, 1).Value = sequentialnumber
        Next sequentialnumber

    End If
    End Sub

Column C contains the list of stuff to be sequentially numbered called as differenttypes and some other info (title etc) that needs to be cut (hence the -5).

Column B contains the sequential numbering. The change to trigger the sub is searched within the sequential numbering range (B4:B30).

When debugging step by step I observe that the lines of code are not executed in the expected order but in another one that to my unexperience eyes seems completely random (such as skipping the loop midway, entering in the middle of the loop after the End sub ).

Question was answered in the comments. It was required to disable the events while inside the loop.

Remove `sequentialnumber = sequentialnumber + 1` for starters as your For loop already goes up by 1 by default. — SJR, Sep 30 '19 at 11:32
Also you need to disable events when changing those cells as otherwise you wilil enter an infinite loop as you trigger the change event again. — SJR, Sep 30 '19 at 11:45
Thanks @SJR for the advice.That piece of code was a remainder of my Do-Until loop checks. — A. Zervakos, Sep 30 '19 at 11:46
You don't need `sequentialnumber = 1`, the `For` loop already handles that. — riskypenguin, Sep 30 '19 at 12:07
Also: There's a typo in your declaration: `Dim differnettypes As Integer` — riskypenguin, Sep 30 '19 at 12:10

score 2 · Answer 1 · answered Sep 30 '19 at 13:08

I compiled the notes from the commentors above to fix your code, hope this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)

Option Explicit

Dim differenttypes As Integer
Dim sequentialnumbering As Range
Dim sequentialnumber As Integer

On Error GoTo Err_Handler
Application.EnableEvents = False

If Not Intersect(Target, Range("B4:B30")) Is Nothing Then

    differenttypes = (Cells(Rows.Count, 3).End(xlUp).Row) - 5
    Set sequentialnumbering = Worksheets("Input").Range("B4:B30")

    For sequentialnumber = 1 To differenttypes
        sequentialnumbering.Cells(sequentialnumber, 1).Value = sequentialnumber
    Next sequentialnumber

End If
Err_Handler:
    Application.EnableEvents = True
End Sub

Sequential numbering in Excel VBA randomly skips orders

Code

1 Answers1