excel - 如何根据另一个工作表中的值填充另一个工作表中的单元格，反之亦然

Question

我正在尝试使用 VBA，以便我可以在 sheet2 的单元格 B7 中输入一个值，然后它会自动填充到 sheet3 的 C7 中，反之亦然。我尝试了下面的代码，但无法正常工作，有什么建议吗？对于一串数字，代码是否也相同？

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo eh
  If Not Intersect(Target, ThisWorkbook.Sheets("sheet 2").Range("B7")) Is Nothing Then
    Application.EnableEvents = False
    ThisWorkbook.Sheets("sheet 3").Range("C" & Target.Row - 0).Value = Target.Value
eh:
Application.EnableEvents = True
    If Err <> 0 Then MsgBox Err & " " & Err.Description, , "Error in Worksheet_Change event, sheet 2"
  End If
  
End Sub

Answer 1

工作簿 SheetChange:工作表单元格中的相同值

注意代码需要复制到ThisWorkbook模块。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     
    Dim wsNames As Variant: wsNames = VBA.Array("sheet 2", "sheet 3")
    Dim CellAddresses As Variant: CellAddresses = VBA.Array("B7", "C7")
     
    Dim iCell As Range
    Dim n As Long
    
    For n = 0 To UBound(wsNames)
        If StrComp(Sh.Name, wsNames(n), vbTextCompare) = 0 Then
            Set iCell = Intersect(Sh.Range(CellAddresses(n)), Target)
            If Not iCell Is Nothing Then
                Application.EnableEvents = False
                    Me.Worksheets(wsNames(1 - n Mod 2)) _
                        .Range(CellAddresses(1 - n Mod 2)).Value = iCell.Value
                Application.EnableEvents = True
            End If
            Exit For
        End If
    Next n

End Sub