工作表区域触发事件（一个区域求相反数），一个区域除以3

xxxxbbhiox

工作表区域触发事件（一个区域求相反数），一个区域除以3


Private Sub Worksheet_Change(ByVal target As Range)
On Error Resume Next
Dim rng As Range, i%
Set rng = Range(Cells(4, 20), Cells(3109, 21))
For i = 1 To 30
Set rng = Union(rng, Range(Cells(1, 20 + i * 21), Cells(3230, 20 + i * 21)))
Next
If Intersect(target, rng) Is Nothing Then Exit Sub
If Val(target) > 0 Then
  Application.EnableEvents = False
  target = -target
  Application.EnableEvents = True
End If
Set rng = Nothing
End Sub



Private Sub Worksheet_Change(ByVal target1 As Range)
On Error Resume Next
Dim rng1 As Range, i%
Set rng1 = Range(Cells(4, 13), Cells(3109, 13))
For i = 1 To 30
Set rng1 = Union(rng1, Range(Cells(1, 13 + i * 13), Cells(3230, 13 + i * 13)))
Next
If Intersect(target1, rng1) Is Nothing Then Exit Sub
If Val(target1) > 0 Then
  Application.EnableEvents = False
  target1 = target1 / 3
  Application.EnableEvents = True
End If
Set rng1 = Nothing
End Sub

xxxxbbhiox

求大神   两个事件帮忙合并一下  要在同一个工作表里实现

xxxxbbhiox

求大神   两个事件帮忙合并一下  要在同一个工作表里实现

opel-wong

xxxxbbhiox 发表于 2019-9-9 14:21
求大神   两个事件帮忙合并一下  要在同一个工作表里实现

试试：

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     If Target.Count > 1 Then Exit Sub
   
3.     If Target.Row > 3 And Target.Row < 3230 Then
   
4.     Dim a
   
5.         If Target.Column = 13 Then
   
6.             If VBA.IsNumeric(Target.Value) And Target.Value <> 0 Then
   
7.                 a = Target.Value
   
8.                 Application.EnableEvents = False
   
9.                 Target.Value = a / 3
   
10.                 Application.EnableEvents = True
    
11.             End If
    
12.         End If
    
13.         ' ----------------
    
14.         If Target.Column = 20 Or Target.Column = 21 Then
    
15.             If VBA.IsNumeric(Target.Value) And Target.Value <> 0 Then
    
16.                 a = Target.Value
    
17.                 Application.EnableEvents = False
    
18.                 Target.Value = a * -1
    
19.                 Application.EnableEvents = True
    
20.             End If
    
21.         End If
    
22.     End If
    
23. End Sub

复制代码