|
本帖最后由 jilinge2 于 2016-2-4 08:43 编辑
王老师,请问下常用凭证那里能简单修改下代码吗?能否添加2个按钮(功能就是科目汇总表把《所有下级科目》和《同时没有月初数、发生额、余额的一级科目》隐藏掉,另一个按钮是显示所有行,这样方便打印科目汇总表。
- Sub 隐藏行()
- Application.ScreenUpdating = False
- For i = 6 To 200
- If Application.Sum(Range("o" & i & ":p" & i)) = 0 And Application.Range("r" & i) = 0 And Application.Range("t" & i) = 0 And Application.Sum(Range("aa" & i & ":ab" & i)) = 0 Or Application.Range("f" & i) > 1 Then Rows(i & ":" & i).Hidden = True
- Next
- Application.ScreenUpdating = True
- Range("D6").Select
- End Sub
- Sub 显示行()
- Cells.Select
- Selection.EntireRow.Hidden = False
- Range("D6").Select
- End Sub
复制代码
修改第32-49行为这样会影响整个表吗?(在第38行添加了一行判断条件,44-46行改了下循环条件,其他都没变)就是结转的时候把没有数据的科目直接跳过去,只把有数的科目填写进入凭证里,因为有时候不注意就会删错行,把有数的删了。
- Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
- If Sheets("数据库").Cells(21, 4) = "会计科目 " Then
- Exit Sub
- ElseIf Target.Row = 2 And Target.Column = 2 Then
- Sheets("首页").Select
- ElseIf Target.Row > 5 And Target.Row < Cells(1, 2) And Target.Column = 7 Then
- Cells(Target.Row + 1, 7).Select
- If Sheets("数据库").Cells(20, 5) = "存货管理" Then
- MsgBox "非常抱歉,在存货管理系统下没有常用凭证操作功能 !", 48, " 不能操作常用凭证 !"
- ElseIf Sheets("数据库").Cells(27, 4) <> "制单" Then
- MsgBox "对不起,您没有此项操作权限 !", 48, " 非法操作 !"
- ElseIf Cells(Cells(1, 2), 11) <> Cells(Cells(1, 2), 12) Then
- MsgBox "您的数据借贷方不平,请检查 !", 48, " 借贷不平 !"
- ElseIf Cells(Target.Row, 7) <> "" And Cells(Target.Row, 8) <> "" And Cells(Target.Row + 1, 8) <> "" _
- And (Target.Row = 6 Or Cells(Target.Row - 1, 8) = "") Then
- If MsgBox("如果本月有关业务已处理完毕,现在需要处理常用凭证,请选择“确定”,否则选择“取消”!", 289, " 要处理常用凭证吗?") = vbOK Then
- With Sheets("常用凭证")
- k = Target.Row
- Sheets("凭证管理").Select
- If Sheets("首页").Cells(3, 4) = "小管家—Excel进销存管理系统" Then
- Sheets("凭证管理").Cells(2, 4) = "记 账 凭 证"
- End If
- If Sheets("数据库").Cells(16, 4) = 5 Then
- ElseIf Sheets("凭证管理").Cells(3, 5) = Sheets("数据库").Cells(Sheets("数据库").Cells(16, 4) - 1, 10) + 1 Then
- Sheets("凭证管理").Cells(3, 13) = 1
- Else
- Sheets("凭证管理").Cells(3, 5) = Sheets("数据库").Cells(Sheets("数据库").Cells(16, 4) - 1, 10)
- Sheets("凭证管理").Cells(3, 6) = Sheets("数据库").Cells(Sheets("数据库").Cells(16, 4) - 1, 11)
- Sheets("凭证管理").Cells(3, 13) = Sheets("数据库").Cells(Sheets("数据库").Cells(16, 4) - 1, 12) + 1
- End If
- 凭证清除
- Application.ScreenUpdating = False
- x = k: y = 5
- Do Until .Cells(x, 8) = ""
- If .Cells(x, 7) <> "" Then
- 摘要 = .Cells(x, 7)
- End If
- If Val(.Cells(x, 11)) <> 0 Or Val(.Cells(x, 12)) <> 0 Then
- pzkmkz = 1: Sheets("凭证管理").Cells(y, 2) = 摘要
- pzkmkz = 1: Sheets("凭证管理").Cells(y, 4) = .Cells(x, 8)
- pzkmkz = 1: Sheets("凭证管理").Cells(y, 10) = .Cells(x, 9)
- pzkmkz = 1: Sheets("凭证管理").Cells(y, 12) = .Cells(x, 11)
- pzkmkz = 1: Sheets("凭证管理").Cells(y, 13) = .Cells(x, 12)
- y = y + 1
- End If
- x = x + 1
- Loop
- Application.ScreenUpdating = True
- x = k
- Do Until .Cells(x, 8) = ""
- If .Cells(x, 14) <> "" And (.Cells(x, 14) - .Cells(x, 13)) < .Cells(x, 12) Then
- .Cells(x, 12) = .Cells(x, 14) - .Cells(x, 13)
- ElseIf .Cells(x, 14) <> "" And (.Cells(x, 14) - .Cells(x, 13)) > .Cells(x, 12) _
- And (.Cells(x, 14) - .Cells(x, 13)) * (2 / 3) < .Cells(x, 12) Then
- .Cells(x, 12) = .Cells(x, 14) - .Cells(x, 13)
- ElseIf .Cells(x, 14) <> "" And .Cells(x, 13) < .Cells(x, 14) Then
- .Cells(x, 13) = .Cells(x, 13) + .Cells(x, 12)
- If .Cells(x, 13) = .Cells(x, 14) Then
- .Cells(x, 12) = ""
- End If
- End If
- x = x + 1
- Loop
- End With
- End If
- End If
- End If
- End Sub
复制代码
|
|