|
楼主 |
发表于 2019-1-15 11:23
|
显示全部楼层
按照要求增加一个2003的模板。
还有增加了合并表与添加目录两个按钮。
模板下载地址如下:
个税抵扣提取信息模板XLS.rar
(128.29 KB, 下载次数: 320)
模块中的代码就是这个。验证了应该是可以运算出来结果,并且有截图在上面。
- Function SheetExist(ByVal Sheetname As String) As Boolean '判断工作表是否存在
- Application.Volatile '易失标记
- Dim s As String, b As Boolean
- b = False
- On Error GoTo bottom
- s = Sheets(Sheetname).Name
- b = True
- bottom: SheetExist = b
- End Function
- Sub 个税提取()
- Dim br(65536, 20)
- Dim a, b, c, x, y, r, rx, ry, n, item
- For Each sht In Sheets
- sht.Activate
- If [a2] <> "个人所得税专项附加扣除信息表" Then GoTo nextsht
- rx = Cells(Rows.Count, 1).End(xlUp).Row
- ry = Cells(5, Columns.Count).End(xlToLeft).Column
- ar = Range(Cells(1, 1), Cells(rx, ry))
- item = item + 1
- br(item - 1, 0) = ar(4, 2) '姓名
- br(item - 1, 1) = "'" & ar(4, 6) '身份证
- br(item - 1, 2) = "'" & ar(5, 7) '手机号码
- '子女扣除
- n = Application.CountIf(Cells, "本人扣除比例")
- For c = 1 To n
- br(item - 1, 3) = br(item - 1, 3) + 1000 * ar(10 + c * 4, 7) / 100
- br(item - 1, 4) = br(item - 1, 4) & "," & ar(7 + c * 4, 3)
- Next c
- br(item - 1, 4) = Mid(br(item - 1, 4), 2, 99) '"共" & c & "个小孩,他们是:" &
- '教育扣除
- c = Application.Match("当前继续教育起始时间", Application.Index(ar, , 2), 0)
- If ar(c, 3) <> "" Then br(item - 1, 5) = 400: br(item - 1, 6) = ar(c, 7) & ar(c, 3)
- '住房贷款
- c = Application.Match("房屋证书号码", Application.Index(ar, , 6), 0)
- If ar(c + 1, 7) = "是" Then br(item - 1, 7) = 500: br(item - 1, 8) = ar(c + 4, 7)
- If ar(c + 1, 7) = "否" Then br(item - 1, 7) = 1000: br(item - 1, 8) = ar(c + 4, 7)
- '租房
- c = Application.Match("租赁期止", Application.Index(ar, , 6), 0)
- If ar(c, 7) <> "" Then br(item - 1, 9) = 1500: br(item - 1, 10) = ar(c - 3, 3)
- '赡养老人
- c = Application.Match("本年度月扣除金额", Application.Index(ar, , 6), 0)
- If ar(c, 7) <> "" Then br(item - 1, 11) = ar(c, 7): br(item - 1, 12) = ar(c, 2)
- '大病扣除
- c = Application.Match("与纳税人关系", Application.Index(ar, , 6), 0)
- If ar(c - 1, 3) <> "" Then br(item - 1, 13) = ar(c, 5): br(item - 1, 14) = ar(c - 1, 3)
- '合计扣除
- br(item - 1, 15) = br(item - 1, 3) + br(item - 1, 5) + br(item - 1, 7) + br(item - 1, 9) + br(item - 1, 11) + br(item - 1, 13)
- nextsht:
- Next sht
- Sheets("总表").Activate: Cells.ClearContents
- [a2].Resize(item, 16) = br: [a1].Resize(1, 16) = Split("姓名,身份证,手机,子女扣除,子女,教育扣除,教育,贷款扣除,贷款银行,租房扣除,出租房,赡养扣除,是否独生子女,大病扣除,大病人,合计扣除", ",")
- ' Stop
- End Sub
- Sub CombineWorkbooks()
- '合并工作薄
- Dim FilesToOpen
- Dim x As Integer
- ' On Error GoTo ErrHandler
- Application.ScreenUpdating = False
- 'Workbooks.Add
- u = ActiveWorkbook.Name
- FilesToOpen = Application.GetOpenFilename(FileFilter:="MicroSoft Excel文件(*.*),*.*", MultiSelect:=True, Title:="要合并的文件")
- If TypeName(FilesToOpen) = "Boolean" Then
- MsgBox "没有选中文件"
- GoTo ExitHandler
- End If
- x = 1
- While x <= UBound(FilesToOpen)
- Workbooks.Open Filename:=FilesToOpen(x)
-
- w = ActiveWorkbook.Name
- w = Replace(w, ".XLSX", "")
- w = Replace(w, ".XLS", "")
- w = Replace(w, "结果", "")
- If Sheets.Count = 1 Then ActiveSheet.Name = Left(Replace(Replace(Split(ActiveWorkbook.Name, ".x")(0), " ", ""), Chr(13), ""), 31)
- For i = 1 To Sheets.Count
- Workbooks(w).Sheets(1).Move after:=Workbooks(u).Sheets(Sheets.Count)
- NewName = Replace(w, ".xls", "") & ActiveSheet.Name
- NewName = Replace(w, ".xlsx", "") & ActiveSheet.Name
- If SheetExist(NewName) Then NewName = NewName & i
- ' ActiveSheet.Name = NewName
- Next
- x = x + 1
- Wend
-
- ExitHandler:
- Application.ScreenUpdating = True
- Exit Sub
- ErrHandler:
- MsgBox Err.Description
- Resume ExitHandler
- End Sub
- Sub mulu()
- 'Application.Calculation = xlManual
- Application.ScreenUpdating = False '取消刷新屏幕以便加快运行代码
- Application.DisplayAlerts = False
- '创建工作表目录
- On Error GoTo r '如遇到错误语句将直接挑到R运行
- Dim i As Integer '定义I,shtcount和SelectionCell变量
- Dim ShtCount As Integer
- Dim SelectionCell As Range
- ShtCount = Worksheets.Count
- If ShtCount = 0 Or ShtCount = 1 Then Exit Sub '工作簿内仅0或1张工作表时候退出
- ' For I = 1 To ShtCount '遍历所有工作表
- If SheetExist("目录") Then
- For x = 1 To 65
- If SheetExist("目录" & x) = 0 Then Sheets("目录").Name = "目录" & x: GoTo mulu2
- If SheetExist("目录" & x) = 0 Then Sheets("目录").Name = "目录" & x + 1: GoTo mulu2
- Next x
- End If
- mulu2:
- Sheets.Add: ActiveSheet.Name = "目录"
- ActiveSheet.Move before:=Sheets(1)
- [a:c].Insert
- ' If SheetExist("目录") Then
- ' For x = 1 To 65
- ' If SheetExist(Sheets("目录" & x)) Then
- ' a = x
- ' Else
- ' Sheets("目录" & x).Name = "目录" & x + 1
- ' End If
- ' Next
- ' Sheets("目录").Name
- ' End If
- ' If SheetExist("目录") Then SheetExist("目录").Delete
- ' Sheets.Add
- ' If Sheets(1).Name <> "目录" Then '当第一张工作表名不为"目录"时候.生成"目录"
- ' ShtCount = ShtCount + 1
- ' Sheets.Add
- ' ActiveSheet.Name = "目录"
- ' ActiveSheet.Move before:=Sheets(1)
- ' End If
- ' Sheets("目录").Select '选定工作表"目录"
- ' Columns("B:B").Delete Shift:=xlToLeft '清除B列
- ' Application.StatusBar = "正在生成目录…………请等待!" '添加等待状态条
- For i = 2 To Sheets.Count '遍历除第一张工作表外所有工作表,建立链接
- ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目录").Cells(i, 2), Address:="", SubAddress:="'" & Sheets(i).Name & "'!R1C1", TextToDisplay:=Sheets(i).Name
- Worksheets("目录").Cells(i, 1) = i - 1
- Next
- Sheets("目录").Select
- Columns("B:B").AutoFit '自动调整
- [b:b].NumberFormatLocal = "@"
- Cells(1, 1) = "号码" 'A列第一个单元格为 "号码"
- Cells(1, 2) = "目录" 'B列第一个单元格录"目录"
- Set SelectionCell = Worksheets("目录").Range("B1")
- With SelectionCell '调整单元格格式
- .HorizontalAlignment = xlDistributed
- .VerticalAlignment = xlCenter
- .AddIndent = True
- .Font.Bold = True
- .Interior.ColorIndex = 34
- End With
- Application.StatusBar = False
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- r:
- Application.Calculation = xlAutomatic
- End Sub
复制代码
|
|