英文:
Why does my Excel table multiply columns when I insert/delete rows?
问题
这个Excel程序有一个表格，其中包含一些列，这些列从多个主表中收集数据。
使用以下代码：
Private Sub worksheet_change(ByVal target As Range)
'在填写Die No之后自动填充Die Description
'如果Project No不为空，则执行以下操作
If Not Intersect(target, Me.ListObjects("F.Main").ListColumns("Project No").DataBodyRange) Is Nothing Then
With target.Offset(0, 1) '在Project No右侧的第1列
.FormulaR1C1 = "=IFerror(INDEX(D.Entry,MATCH(rc[-1],D.Entry[Project No],0),2),"""")"
.Value = .Value
End With
With target.Offset(0, 2)
.FormulaR1C1 = "=IFerror(INDEX(D.Entry,MATCH(rc[-2],D.Entry[Project No],0),3),"""")"
.Value = .Value
End With
With target.Offset(0, 3)
.FormulaR1C1 = "=IFerror(INDEX(D.Entry,MATCH(rc[-3],D.Entry[Project No],0),4),"""")"
.Value = .Value
End With
.
.
.
End If
当我尝试在表格中插入或删除新行时出现以下问题：
这是什么原因造成的？
英文:
This Excel program have a table which have some columns which collects data from several master tables.
Using the code like so:
Private Sub worksheet_change(ByVal target As Range)
'Autofill Die Description after Die No is filled
'If Project No is not empty then do the following
If Not Intersect(target, Me.ListObjects("F.Main").ListColumns("Project No").DataBodyRange) Is Nothing Then
With target.Offset(0, 1) '1 column to the right of Project No
.FormulaR1C1 = "=IFerror(INDEX(D.Entry,MATCH(rc[-1],D.Entry[Project No],0),2),"""")"
.Value = .Value
End With
With target.Offset(0, 2)
.FormulaR1C1 = "=IFerror(INDEX(D.Entry,MATCH(rc[-2],D.Entry[Project No],0),3),"""")"
.Value = .Value
End With
With target.Offset(0, 3)
.FormulaR1C1 = "=IFerror(INDEX(D.Entry,MATCH(rc[-3],D.Entry[Project No],0),4),"""")"
.Value = .Value
End With
.
.
.
End If
When I try to insert or delete new rows in that table this happens:
What is causing this?
答案1
得分: 1
我使用以下解决方案：
我在名为Main的模块中定义了一个全局变量
Public EnableEvents As Boolean
在初始化阶段，我将其设置为True。然后在Worksheet_change例程中
Private Sub worksheet_change(ByVal target As Range)
If Main.EnableEvents Then
Main.EnableEvents = False
如果不相交... 在此处粘贴您的实际代码
Main.EnableEvents = True
End If
End Sub
英文:
I use the following solution:
I define a global var in a module named Main
Public EnableEvents As Boolean
In the initialization phase I set it to True. Then in the Worksheet_change routine
Private Sub worksheet_change(ByVal target As Range)
If Main.EnableEvents Then
Main.EnableEvents = False
If Not Intersect ... copy your actual code here
Main.EnableEvents = True
Endif
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论