本人在学习excel vba，希望用宏完成自动取数的填写社保

燠风

社保.rar (116.49 KB, 下载次数: 43)

2014-4-24 10:36 上传

点击文件名下载附件

本人用if函数实现了大部分情况可以用的自动填写社保进社保审核明细表，现在想用vba完成这一工作，顺便学习下vba，请高手指点下，希望把1.xls的表里面的数据填进社保审核明细表对应的地方，比如1。xls 就填进1月，养老保险就填进养老保险，一般来说社保审核明细表是不会变的，每个月的社保格式一般也不会变，但它有时会缺少一个两个险种，或多一两个险种，也就是说它里面的列数是会变动的，人数会变动。

燠风

1. Sub 社保()
   
2.     For l = 1 To 12 Step 1
   
3.     Workbooks.Open Filename:="F:\工作1\宏\社保" & l & ".xls"
   
4. '文件路径根据需要修改
   
5.     Range("L4").Select
   
6.     ActiveCell.FormulaR1C1 = "1"
   
7.     Range("L4").Select
   
8.     Selection.Copy
   
9.     Range("E9:BJ452").Select
   
10.     Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
    
11.         SkipBlanks:=False, Transpose:=False
    
12. '上述代码用于文本转数字
    
13.     For i = 4 To 30 Step 1
    
14.    If Cells(7, i) = "基本养老保险(非本市城镇户籍)" Then If Cells(8, i + 1) = "单位" Then Cells(3, 79) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
15.    
    
16.    Next i
    
17.     For i = 4 To 30 Step 1
    
18.    If Cells(7, i) = "基本养老保险(非本市城镇户籍)" Then If Cells(8, i + 2) = "个人" Then Cells(3, 80) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
19.    
    
20.    Next i
    
21.    
    
22.     For i = 4 To 30 Step 1
    
23.    If Cells(7, i) = "基本养老保险" Then If Cells(8, i + 1) = "单位" Then Cells(4, 79) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
24.    
    
25.    Next i
    
26.     For i = 4 To 30 Step 1
    
27.    If Cells(7, i) = "基本养老保险" Then If Cells(8, i + 2) = "个人" Then Cells(4, 80) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
28.    
    
29.    Next i
    
30.    
    
31.    
    
32.     For i = 4 To 30 Step 1
    
33.    If Cells(7, i) = "基本医疗保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 81) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
34.    
    
35.    Next i
    
36.     For i = 4 To 30 Step 1
    
37.    If Cells(7, i) = "基本医疗保险" Then If Cells(8, i + 2) = "个人" Then Cells(3, 82) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
38.    
    
39.    Next i
    
40.     For i = 4 To 30 Step 1
    
41.    If Cells(7, i) = "工伤保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 85) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
42.    
    
43.    Next i
    
44.     For i = 4 To 30 Step 1
    
45.    If Cells(7, i) = "生育保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 86) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
46.    
    
47.    Next i
    
48.     For i = 4 To 30 Step 1
    
49.    If Cells(7, i) = "重大疾病医疗补助" Then If Cells(8, i + 1) = "单位" Then Cells(3, 87) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
50.    
    
51.    Next i
    
52.     For i = 4 To 30 Step 1
    
53.    If Cells(7, i) = "失业保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 83) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
54.    
    
55.    Next i
    
56.     For i = 4 To 30 Step 1
    
57.    If Cells(7, i) = "失业保险" Then If Cells(8, i + 2) = "个人" Then Cells(3, 84) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
58.        Next i
    
59.    For i = 4 To 30 Step 1
    
60.    If Cells(7, i) = "城镇职工补充医疗保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 88) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
61.    
    
62.    Next i
    
63.    For i = 4 To 30 Step 1
    
64.    If Cells(7, i) = "失业保险(农民工)" Then If Cells(8, i + 1) = "单位" Then Cells(4, 83) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
65.    
    
66.    Next i
    
67.    
    
68.    Next l
    
69.    For p = 1 To 12 Step 1
    
70.    Windows("" & p & ".XLS").Activate
    
71.     Range("CA3:CJ3").Select
    
72.     Selection.Copy
    
73.     Windows("社保明细审核表.xls").Activate
    
74.     Range("B" & p + 8).Select
    
75.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
76.         :=False, Transpose:=False
    
77.     Windows("" & p & ".XLS").Activate
    
78.     Range("CA4:CJ4").Select
    
79.     Application.CutCopyMode = False
    
80.     Selection.Copy
    
81.     Windows("社保明细审核表.xls").Activate
    
82.     Range("B" & p + 8).Select
    
83.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
    
84.         :=False, Transpose:=False
    
85.         Next p
    
86.         Workbooks.Close
    
87. End Sub
    

复制代码

燠风

1. 社保明细审核表.rar (16.91 KB, 下载次数: 18)
   2014-5-29 15:18 上传

   点击文件名下载附件
   Sub 社保1()
   
2.     Dim FileName As String
   
3.     Dim r As Integer
   
4.     FileName = Dir(ThisWorkbook.Path & "\*.xls")
   
5.     Do While FileName <> ""
   
6.      If FileName <> ThisWorkbook.Name Then  k = ThisWorkbook.Path
   
7.      Workbooks.Open k & "" & FileName
   
8.     r = Left(FileName, 2)
   
9.     Range("L4").Select
   
10.     ActiveCell.FormulaR1C1 = "1"
    
11.     Range("L4").Select
    
12.     Selection.Copy
    
13.     Range("E9:BJ452").Select
    
14.     Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
    
15.         SkipBlanks:=False, Transpose:=False
    
16.     For i = 4 To 30 Step 1
    
17.    If Cells(7, i) = "基本养老保险(非本市城镇户籍)" Then If Cells(8, i + 1) = "单位" Then Cells(3, 79) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
18.    
    
19.    Next i
    
20.     For i = 4 To 30 Step 1
    
21.    If Cells(7, i) = "基本养老保险(非本市城镇户籍)" Then If Cells(8, i + 2) = "个人" Then Cells(3, 80) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
22.       Next i
    
23.       For i = 4 To 30 Step 1
    
24.    If Cells(7, i) = "基本养老保险" Then If Cells(8, i + 1) = "单位" Then Cells(4, 79) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
25.       Next i
    
26.     For i = 4 To 30 Step 1
    
27.    If Cells(7, i) = "基本养老保险" Then If Cells(8, i + 2) = "个人" Then Cells(4, 80) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
28.     Next i
    
29.      For i = 4 To 30 Step 1
    
30.    If Cells(7, i) = "基本医疗保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 81) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
31.       Next i
    
32.     For i = 4 To 30 Step 1
    
33.    If Cells(7, i) = "基本医疗保险" Then If Cells(8, i + 2) = "个人" Then Cells(3, 82) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
34.      Next i
    
35.     For i = 4 To 30 Step 1
    
36.    If Cells(7, i) = "工伤保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 85) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
37.     Next i
    
38.     For i = 4 To 30 Step 1
    
39.    If Cells(7, i) = "生育保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 86) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
40.      Next i
    
41.     For i = 4 To 30 Step 1
    
42.    If Cells(7, i) = "重大疾病医疗补助" Then If Cells(8, i + 1) = "单位" Then Cells(3, 87) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
43.      Next i
    
44.     For i = 4 To 30 Step 1
    
45.    If Cells(7, i) = "失业保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 83) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
46.    
    
47.    Next i
    
48.     For i = 4 To 30 Step 1
    
49.    If Cells(7, i) = "失业保险" Then If Cells(8, i + 2) = "个人" Then Cells(3, 84) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 2), Cells(300, i + 2)))
    
50.        Next i
    
51.    For i = 4 To 30 Step 1
    
52.    If Cells(7, i) = "城镇职工补充医疗保险" Then If Cells(8, i + 1) = "单位" Then Cells(3, 88) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
53.    
    
54.    Next i
    
55.    For i = 4 To 30 Step 1
    
56.    If Cells(7, i) = "过渡性基本医疗保险金" Then If Cells(8, i + 1) = "单位" Then Cells(4, 88) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
57.    
    
58.    Next i
    
59.    For i = 4 To 30 Step 1
    
60.    If Cells(7, i) = "失业保险(农民工)" Then If Cells(8, i + 1) = "单位" Then Cells(4, 83) = Application.WorksheetFunction.Sum(Range(Cells(9, i + 1), Cells(300, i + 1)))
    
61.    
    
62.    Next i
    
63.     Windows("" & r & ".XLS").Activate
    
64.     Range("CA3:CJ3").Select
    
65.     Selection.Copy
    
66.     Windows("社保明细审核表.xls").Activate
    
67.     Range("B" & r + 8).Select
    
68.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    
69.         :=False, Transpose:=False
    
70.     Windows("" & r & ".XLS").Activate
    
71.     Range("CA4:CJ4").Select
    
72.     Application.CutCopyMode = False
    
73.     Selection.Copy
    
74.     Windows("社保明细审核表.xls").Activate
    
75.     Range("B" & r + 8).Select
    
76.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
    
77.         :=False, Transpose:=False
    
78.   End If
    
79.         FileName = Dir    ' 用Dir 函数取得其他文件名，并赋给变量
    
80.    Loop
    
81.    Workbooks.Close
    
82. End Sub
    
83. 
    
84. 
    

复制代码

楼上的当文件少了1个就会出现下标越界，比楼上好在，如果不是12个文件也可以运行，可以不用改文件路径名了，放在其他地方也可以用，但是该文件夹要只有这些文件，如果有其他不相干的文件就会出错