时间戳

首页 > 解决方案 > 具有最小差异百分比的工作表更改事件

excel - 具有最小差异百分比的工作表更改事件

问题描述

我有一个工作表更改事件。

原始代码：

Private Sub Worksheet_Change(ByVal Target As Range)
   If Range("J12") < Range("G12") Then
      MsgBox "Oppps. Your system is not working!"
   End If
End Sub

我正在尝试将代码扩展为：

一个。将范围从单个单元格（J12 和 G12）增加到扩展单元格范围（J12:42、G12:42）。

湾。不是由小于 (J12 < G12) 触发更改事件，而是由 J12 < G12 之间的百分比差异触发。

标签： excelvba

解决方案

这是更新的代码。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim diffPercent
    'Check that the data is changed between row 12 and 42 and it is even row. eg 12,14,16...42.
    If (Target.Row > 10 And Target.Row < 44) And ((Target.Row Mod 2) = 0) Then  'And _
            '(Target.Column = 7 Or Target.Column = 10) Then

        'Get the values in J ang G columns of that particular row.
        number1 = Range("G" & Target.Row).Value
        number2 = Range("J" & Target.Row).Value

        'Check for presence of both the inputs to calculate difference in percentage.
        If Not chkInputs(number1, number2) Then
            Exit Sub
        End If
        'Calculate the percentage difference.
        diff = number2 - number1
        diffPercent = (diff / number2) * 100

        'Give alert if difference more than 10 percent
        If diffPercent > 10 Then
            MsgBox "Oppps. Your system is not working! The difference is :" & diff & "|" & diffPercent
        End If
    End If

End Sub

Function chkInputs(number1, number2)
chkInputs = False
If IsNumeric(number1) And IsNumeric(number2) Then
    chkInputs = True
End If

End Function

推荐阅读