excel - 需要清理多行订单表单上的 If Not Intersect Is Nothing 代码

Question

VBA 新手。想知道如何使我的代码更高效。

我创建了一个包含 50 个订单行的订单表格，即您最多可以使用此表订购 50 个项目。每个项目都有 3 种可能的定制，这将决定产品 SKU。如果您更改每行的项目选择，我已经编写了以下代码来“重置”自定义选择（仅显示前 3 行的代码，但对所有 50 行重复）。任何帮助，将不胜感激。

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("L1RoomType")) Is Nothing Then
    Range("L1Dis").Value = Range("L1DisDefault").Value
End If
If Not Intersect(Target, Range("L1RoomType")) Is Nothing Then
    Range("L1Pwr").Value = Range("L1PwrDefault").Value
End If
If Not Intersect(Target, Range("L1RoomType")) Is Nothing Then
    Range("L1TM").Value = Range("L1TMDefault").Value
End If
If Not Intersect(Target, Range("L2RoomType")) Is Nothing Then
    Range("L2Dis").Value = Range("L2DisDefault").Value
End If
If Not Intersect(Target, Range("L2RoomType")) Is Nothing Then
    Range("L2Pwr").Value = Range("L2PwrDefault").Value
End If
If Not Intersect(Target, Range("L2RoomType")) Is Nothing Then
    Range("L2TM").Value = Range("L2TMDefault").Value
End If
If Not Intersect(Target, Range("L3RoomType")) Is Nothing Then
    Range("L3Dis").Value = Range("L3DisDefault").Value
End If
If Not Intersect(Target, Range("L3RoomType")) Is Nothing Then
    Range("L3Pwr").Value = Range("L3PwrDefault").Value
End If
If Not Intersect(Target, Range("L3RoomType")) Is Nothing Then
    Range("L3TM").Value = Range("L3TMDefault").Value
End If
    
Application.EnableEvents = True

End Sub

标签： excelvba

Answer 1

利用命名中的模式。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IntersectRange As Range
    Dim RoomNum As Long
    
    On Error GoTo EH ' Ensure events get turned back on
    Application.EnableEvents = False
    
    For RoomNum = 1 To 3 ' or 50?
        Set IntersectRange = Intersect(Target, Me.Range("L" & RoomNum & "RoomType"))
        If Not IntersectRange Is Nothing Then
            Me.Range("L" & RoomNum & "Dis").Value = Me.Range("L" & RoomNum & "DisDefault").Value
            Me.Range("L" & RoomNum & "Pwr").Value = Me.Range("L" & RoomNum & "PwrDefault").Value
            Me.Range("L" & RoomNum & "TM").Value = Me.Range("L" & RoomNum & "TMDefault").Value
        End If
    Next
EH:
    Application.EnableEvents = True
End Sub