☰

Excel，如何为工作表中的单个单元格运行多个宏？

tag-icon • tag-icon microsoft-excel macros
Excel，如何为工作表中的单个单元格运行多个宏？

我有一个仅适用于单元格 D3 和 E3 的代码，我需要它对其他单元格（如 D4/E4、D5/E5）执行相同的操作，但在同一工作表上使用不同的公式。我正在尝试为其他单元格创建新的私有子项，但它们似乎无法编译？提前致谢：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DE As Range, t As Range, v As Variant
    Dim r As Long
    Set t = Target
    Set DE = Range("D3:E3")
    If Intersect(t, DE) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r = t.Row
        v = t.Value
        If v = "" Then
            Range("D" & r & ":E" & r).Value = ""
        End If
        If IsNumeric(v) Then
            If Intersect(t, Range("E3:E3")) Is Nothing Then
                t.Offset(0, 1).Value = v * 0.0393701
            Else
                t.Offset(0, -1).Value = v / 0.0393701
            End If
        End If
    Application.EnableEvents = True
End Sub

答案1

现在修复了：

Private Sub Worksheet_Change(ByVal Target As Range) Dim BC As Range, t As Range, v As Variant Dim r As Long Set t = Target Set BC = Range("D:E") 如果 Intersect(t, BC) 为 Nothing 则退出子程序 Application.EnableEvents = False r = t.Row v = t.Value

    If IsNumeric(v) Then
        If Intersect(t, Range("E3")) Is Nothing Then
            Range("E3").Formula = Range("D3") * 0.0393701

        Else
            Range("D3").Formula = Range("E3") / 0.0393701

        End If

         If Intersect(t, Range("E4")) Is Nothing Then
            Range("E4").Formula = Range("D4") * 3.28084

        Else
            Range("D4").Formula = Range("E4") / 3.28084

        End If

            If Intersect(t, Range("E5")) Is Nothing Then
            Range("E5").Formula = Range("D5") * 14.5038

        Else
            Range("D5").Formula = Range("E5") / 14.5038

        End If

                        If Intersect(t, Range("E6")) Is Nothing Then
            Range("E6").Formula = Range("D6") * 14.5038

        Else
            Range("D6").Formula = Range("E6") / 14.5038

        End If

                        If Intersect(t, Range("E7")) Is Nothing Then
            Range("E7").Formula = Range("D7") * 14.5038

        Else
            Range("D7").Formula = Range("E7") / 14.5038

        End If

                        If Intersect(t, Range("E9")) Is Nothing Then
            Range("E9").Formula = Range("D9") * 0.02628

        Else
            Range("D9").Formula = Range("E9") / 0.02628

        End If

                        If Intersect(t, Range("E10")) Is Nothing Then
            Range("E10").Formula = Range("D10") * 35.3147

        Else
            Range("D10").Formula = Range("E10") / 35.3147

        End If

 End If
Application.EnableEvents = True

子目录结束

相关内容