VBA监测单元格改动；获取月份最后一天的方法

日常工作有种场景，就是EXCEL单元格值发生改变，其他单元格的值跟着做相应的改变

这种的实现方法是在Worksheet的Change事件中写入变操作逻辑。

通过Target.Column和Target.Row，获得发生变动的单元格的列号，行号，判断列号和行号是否在我们监测的区域。

随之发生改变的单元格，值变动也会触发Change事件，如果随之变动的单元格也在我们监测的区域，会导致Change事件被无限触发，所以在里面一定要加入阻止无限触发的机制：

Application.EnableEvents = False '关闭事件处理，阻止循环执行

程序的结尾一定要将上面重设为True

里面还有个获取月份最后一天的方法：

y = Cells(1, 1).Value
m = Cells(1, 3).Value
last_day = Day(DateSerial(y, m + 1, 1) - 1)

 

 

 

Private Sub Worksheet_Change(ByVal Target As Range)

'MsgBox (Target.Column & "," & Target.Row)
If Target.Column = 1 And Target.Row = 1 Then
    Application.EnableEvents = False '关闭事件处理，阻止循环执行
    y = Cells(1, 1).Value
    m = Cells(1, 3).Value
    last_day = Day(DateSerial(y, m + 1, 1) - 1)
    'Debug.Print (last_day)
    
    Range("F2:G32").ClearContents
    For i = 1 To last_day
        Cells(i + 1, 6).Value = y & "/" & m & "/" & i
        tar_row = Sheet2.Range("A:A").Find(Cells(i + 1, 6).Value).Row
        Cells(i + 1, 7).Value = Sheet2.Cells(tar_row, 2).Value
    Next
    Application.EnableEvents = True '阻止循环执行

End If

If Target.Column = 3 And Target.Row = 1 Then
    Application.EnableEvents = False '阻止循环执行
    y = Cells(1, 1).Value
    m = Cells(1, 3).Value
    last_day = Day(DateSerial(y, m + 1, 1) - 1)
    
    
    Range("F2:G32").ClearContents
    For i = 1 To last_day
        Cells(i + 1, 6).Value = y & "/" & m & "/" & i
        tar_row = Sheet2.Range("A:A").Find(Cells(i + 1, 6).Value).Row
        Cells(i + 1, 7).Value = Sheet2.Cells(tar_row, 2).Value
    Next
    Application.EnableEvents = True '阻止循环执行
End If

c_y = Cells(1, 1).Value
c_m = Cells(1, 3).Value
c_last_day = Day(DateSerial(c_y, c_m + 1, 1) - 1)
If Target.Column = 7 And Target.Row > 1 And Target.Row <= c_last_day Then
    Application.EnableEvents = False '阻止循环执行
    y = Cells(1, 1).Value
    m = Cells(1, 3).Value
    last_day = Day(DateSerial(y, m + 1, 1) - 1)
    month_total = last_day * Cells(3, 3).Value
    r1 = Target.Row
    Debug.Print ("r1=" & r1)
    
    sum1 = 0
    For i = 2 To r1
        sum1 = sum1 + Cells(i, 7).Value
    Next
    Debug.Print ("sum1=" & sum1)
    
    net_rows = last_day - (r1 - 1)
    Debug.Print ("net_rows=" & net_rows)
    
    aver_num = (month_total - sum1) / net_rows
    Debug.Print ("aver_num=" & aver_num)
    
    For i = (r1 + 1) To (last_day + 1)
        Cells(i, 7) = aver_num
    Next
    Application.EnableEvents = True '阻止循环执行
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub