|
因为你使用了宏表函数,在打开的时候应该让其更新一下公式(请你用心找不同吧^_^)
本帖最后由 suye1010 于 2013-1-16 18:05 编辑
- Sub 生成各参保单位托收数据()
- Dim wj As String, fs As Object, arr
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.EnableEvents = False
- Range("a3:s500").ClearContents
- wj = Dir(ThisWorkbook.Path & "\数据库")
- s = 2
- Do While wj <> ""
- If s = 2 Then
- ReDim arr(1 To 19, 1 To 1)
- Else
- ReDim Preserve arr(1 To 19, 1 To UBound(arr, 2) + 1)
- End If
- s = s + 1
- Set fs = GetObject(ThisWorkbook.Path & "\数据库" & wj)
- fs.Sheets(1).Calculate
- arr(1, s - 2) = s - 2
- arr(2, s - 2) = Mid(wj, 6, Len(wj) - 9)
- arr(3, s - 2) = fs.Sheets(1).[g2]
- arr(4, s - 2) = fs.Sheets(1).[n2]
- arr(5, s - 2) = Sheets(1).[j2]
- arr(6, s - 2) = Application.WorksheetFunction.CountIf(fs.Sheets(1).Range("u6:u1000"), "在职")
- arr(7, s - 2) = Application.WorksheetFunction.CountIf(fs.Sheets(1).Range("u6:u1000"), "退休")
- arr(8, s - 2) = Application.WorksheetFunction.CountIf(fs.Sheets(1).Range("u6:u1000"), "停缴")
- arr(9, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("u6:u1000"), "在职", fs.Sheets(1).Range("r6:r1000"))
- arr(10, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("u6:u1000"), "在职", fs.Sheets(1).Range("r6:r1000")) * 0.18
- arr(11, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("u6:u1000"), "在职", fs.Sheets(1).Range("r6:r1000")) * 0.02
- arr(12, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("W6:W1000"), "托", fs.Sheets(1).Range("r6:r1000"))
- arr(13, s - 2) = "=""养老保险:缴费人数:""&SUM(RC[-7])&""人"""
- arr(14, s - 2) = _
- "=""单位缴存:""&TEXT(RC[-5],""0.00"")&""""&""×""&18%&""=""&TEXT(RC[-5]*18%,""0.00"")&"""""
- arr(15, s - 2) = _
- "=""个人缴存:""&TEXT(RC[-6],""0.00"")&""""&""×""&2%&""=""&TEXT(RC[-6]*2%,""0.00"")&"""""
- arr(16, s - 2) = _
- "=""返纳金:""&TEXT(RC[-4],""0.00"")&""""&""×""&18%&""=""&TEXT(RC[-4]*18%,""0.00"")&"""""
- arr(17, s - 2) = Application.WorksheetFunction.SumIf(fs.Sheets(1).Range("W6:W1000"), "托", fs.Sheets(1).Range("r6:r1000")) * 0.18
- arr(18, s - 2) = "=VLOOKUP(R1C4,年度月份设置!R2C3:R13C4,2,0)"
- arr(19, s - 2) = "=SUM(RC[-9]+RC[-8]+RC[-2])"
- fs.Close False
- wj = Dir
- Loop
- Cells(3, 1).Resize(UBound(arr, 2), UBound(arr)) = Application.Transpose(arr)
- Application.EnableEvents = True
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
查看全部评分
|