我有一张工作表,需要单个单元格镜像另一张工作表上的单个单元格,如果发生更改,反之亦然。我在超级用户上找到了以下代码:
对于工作表 1:
Private Sub Worksheet_change(ByVal Target as Range)
Sheets("Sheet2").Range("B7").Value = Target.Value
End Sub
对于工作表 2:
Private Sub Worksheet_change(ByVal Target as Range)
If Sheets("Sheet1").Range("A4").Value <> Target.Value Then
Sheets("Sheet1").Range("A4").Value = Target.Value
End if
End Sub
但我无法让它工作。我收到一条错误消息:
运行时错误‘9’:
下标超出范围。
调试指向这一行:
Sheets("Sheet2").Range("B7").Value = Target.Value
我到底做错了什么?
谢谢!
答案1
假设我想镜像单元格Sheet1.[A4]
带有电池Sheet2.[B7]
,我将使用以下内容:
工作表1
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'Sheet1 Module
If Not Intersect(Target, [A4]) Is Nothing Then 'Update [B7] only when [A4] is updated
MirrorCells Worksheets("Sheet2").[B7], Target
End If
End Sub
工作表2
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'Sheet2 Module
If Not Intersect(Target, [B7]) Is Nothing Then 'Update [A4] only when [B7] is updated
MirrorCells Worksheets("Sheet1").[A4], Target
End If
End Sub
模块1(标准模块)
Option Explicit
Public Sub MirrorCells(ByRef oldVal As Range, ByRef newVal As Range)
If newVal.CountLarge = 1 Then 'Check that updated range is 1 cell (paste operations)
If Not IsError(oldVal) And Not IsError(newVal) Then 'Ignore errors
If oldVal <> newVal Then 'If oldVal needs to be updated
Application.EnableEvents = False 'Stop all events
oldVal.Value = newVal.Value 'Update it (triggers a new event)
Application.EnableEvents = True 'Turn events back on
End If
End If
End If
End Sub
Run-time error '9'
是因为您没有完全命名的工作表而生成的"Sheet2"
检查选项卡名称中是否有多余的空格,例如"Sheet 2"
, 或"Sheet2 "
,或" Sheet2"