时间戳

首页 > 解决方案 > 如何在excel VBA中组合两个不同列的两个不同请求

excel - 如何在excel VBA中组合两个不同列的两个不同请求

问题描述

我想要一个自动将给定列乘以设定数字的宏。这个数字对于每一列都是不同的。到目前为止，我只能将它应用于一列。我不能为任何其他人完成它：

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value * 300
    Application.EnableEvents = True
    


If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value * 500
    Application.EnableEvents = True

End Sub

标签： excelvba

解决方案

或许是这样的：

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False

    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("B:B")

    If Not rng Is Nothing Then
        rng.Value = rng.Value * 300
    End If
    
    Set rng = Intersect(Target, Me.Range("C:C"))
    If Not rng Is Nothing Then
        rng.Value = rng.Value * 500
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

编辑：如果您有两个以上的列（并且它们是连续的），这可能是一种更好的方法：

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Me.Range("B:F")) Is Nothing Then
        On Error GoTo SafeExit
        Application.EnableEvents = False
        
        Dim rng as Range
        For Each rng In Intersect(Target, Me.Range("B:F"))
            Dim multiplier As Long
            
            Select Case rng.Column
                Case 2 ' column B
                    multiplier = 300
                Case 3 ' column C
                    multiplier = 500
                Case 4 ' column D
                    multiplier = 400
                Case 5 ' column E
                    multiplier = ...
                Case 6 ' column F
                    multiplier = ...
            End Select

            If IsNumeric(rng.Value) Then
                rng.Value = rng.Value * multiplier
            End If   
        Next 
    End If

SafeExit:
    Application.EnableEvents = True
End Sub

推荐阅读