excel VBA 编程-蒲公英云

excel VBA 编程

Dim cuttent_columns As Integer
Dim care_repeat As Integer
Private Sub Workbook_Open()
    Dim i As Integer
    Dim j As Integer
    '确定cuttent_columns的值
    For i = 1 To 100
        If Cells(1, i) = "" Then
            For j = i To 100
                If Cells(1, j) = "" Then
                Else
                    cuttent_columns = j - 1
                    'MsgBox "init cuttent_columns"
                    'MsgBox cuttent_columns
                    Exit Sub
                End If
            Next
        End If
    Next
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next  '跳过异常
    Application.ScreenUpdating = False '关闭屏幕更新
     If care_repeat = 1 Then
        care_repeat = 0
        'MsgBox "防止正反馈"
        Exit Sub
    End If
    If Target.Row > 51 Then Exit Sub
    'MsgBox "cuttent_columns"
    'MsgBox cuttent_columns
    'MsgBox "Target.Row"
    'MsgBox Target.Row
    'MsgBox "Target.Target.Column"
    'MsgBox Target.Column
    Dim null_flag As Integer
    Dim i As Integer
    null_flag = 0
    If Cells(Target.Row, 2) = 0 Or Cells(Target.Row, 2) = "" Then
    Else
        For i = 2 To cuttent_columns
            If Cells(Target.Row, i) = "" Then
                care_repeat = 1
                Cells(Target.Row, i) = Cells(Target.Row, 2)
                care_repeat = 1
                Cells(Target.Row, 2) = 0
                null_flag = 1
                Exit For
            End If
        Next
        If null_flag = 0 Then
            cuttent_columns = cuttent_columns + 1
            care_repeat = 1
            ActiveSheet.Columns(cuttent_columns).Insert
            care_repeat = 1
            Cells(Target.Row, cuttent_columns) = Cells(Target.Row, 2)
            care_repeat = 1
            Cells(Target.Row, 2) = 0
        End If
    End If
    '计算总和
    Dim littel_total As Integer
    Dim littel_total_num As Integer
    Dim big_total_num As Integer
    littel_total = cuttent_columns + 1
    littel_total_num = 0
    big_total_num = 0
    '计算小总和
    For i = 3 To cuttent_columns
        littel_total_num = littel_total_num + Cells(Target.Row, i)
    Next
    care_repeat = 1
    Cells(Target.Row, littel_total) = littel_total_num
    '判断
    If Cells(Target.Row, littel_total) > Cells(53, 2) Then
        care_repeat = 1
        Cells(Target.Row, littel_total + 1) = "金额超过阈值"
    Else
        care_repeat = 1
        Cells(Target.Row, littel_total + 1) = ""
    End If
    '计算大总和
    For i = 2 To 51
        big_total_num = big_total_num + Cells(i, littel_total)
    Next
    care_repeat = 1
    Cells(52, littel_total) = big_total_num
    Application.ScreenUpdating = True '恢复屏幕更新
End Sub