Excel VBA 中的顺序编号随机跳过订单

Question

我尝试根据任何单元格更改为列表创建顺序编号方法

我试图用做和做直到循环来改变下一个，但没有任何成功

---

法典

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

---

C列包含要按顺序编号的内容列表，称为不同类型，以及一些需要剪切的其他信息(标题等((因此为-5(。

列 B 包含顺序编号。触发子的更改在顺序编号范围 (B4：B30( 内搜索。

在逐步调试时，我观察到代码行没有按预期顺序执行，而是在另一个在我没有经验的眼睛看来完全随机的行中执行(例如中途跳过循环，在 End sub 之后进入循环中间 ).

问题在评论中得到了回答。需要在循环内禁用事件。

Answer 1

我编译了上面评论者的注释来修复您的代码，希望这对您有用：

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