案例
案例来源：Excel和Access （微信公众号）点击 - 查看原文
指定特定部门，增加或者减少工资，并汇总合计工资。
附件：点击查看-百度云
提取密码：nu7c
1.源数据代码
Sub 案例源数据()
Dim Al, Bl, Cl, Dl, El, Fl As Integer
Range("a1:f1").Merge '合并单元格
Range("a1").HorizontalAlignment = xlCenter '居中
Range("a2:f11").Borders.LineStyle = 1 '加边框
Cells(1, 1) = "***公司8月份工资表" 'cells对单元格赋值，cells(行数，列数)
Cells(2, 1) = "工号"
Cells(2, 2) = "姓名"
Cells(2, 3) = "所在部门"
Cells(2, 4) = "基本工资"
Cells(2, 5) = "加班费"
Cells(2, 6) = "总工资"
For Al = 1 To 9
Cells(2 + Al, 1) = "GH00" & Al
Next
Cells(3, 2) = "赵薇"
Cells(4, 2) = "黄渤"
Cells(5, 2) = "章子怡"
Cells(6, 2) = "杨幂"
Cells(7, 2) = "何炅"
Cells(8, 2) = "周迅"
Cells(9, 2) = "胡歌"
Cells(10, 2) = "刘亦菲"
Cells(11, 2) = "李诗诗"
Cells(3, 3) = "Excel部"
Cells(4, 3) = "Outlook部"
Cells(5, 3) = "PPT部"
Cells(6, 3) = "Outlook部"
Cells(7, 3) = "Word部"
Cells(8, 3) = "Excel部"
Cells(9, 3) = "Word部"
Cells(10, 3) = "Outlook部"
Cells(11, 3) = "PPT部"
Cells(3, 4) = 3000
Cells(4, 4) = 4400
Cells(5, 4) = 3500
Cells(6, 4) = 4400
Cells(7, 4) = 5000
Cells(8, 4) = 3000
Cells(9, 4) = 5000
Cells(10, 4) = 4400
Cells(11, 4) = 3500
Cells(3, 5) = 35
Cells(4, 5) = 56
Cells(5, 5) = 243
Cells(6, 5) = 65
Cells(7, 5) = 754
Cells(8, 5) = 4
Cells(9, 5) = 465
Cells(10, 5) = 64
Cells(11, 5) = 27
For Fl = 1 To 9
Cells(2 + Fl, 6) = Cells(2 + Fl, 4) + Cells(2 + Fl, 5)
Next
End Sub
2.增加工资代码
Sub 增加指定部门人员工资()
Dim Bumen As Variant
Dim I As Integer
Dim Gongzi As Integer
If MsgBox("您确定要修改工人工资吗？", vbOKCancel, "提醒") = vbOK Then '提示，是否确定要调整薪资
Set Bumen = Range("c3:c" & Range("c2000").End(xlUp).Row).Find(what:=InputBox("请输入需要修改工资的部门名称", "查找", "Excel部"), LookIn:=xlValues, searchorder:=xlRows)
'在C列部门里，查找输入的部门名称，并设置Bumen=输入的部门名称。为Find查找加Inputbox嵌套
If Bumen Is Nothing Then '判断，查找的部门是否存在。若不存在，则退出VBA，并提示没有对应数据
MsgBox "没有找到输入的部门，请重新检查", vbCritical, "提醒"
Exit Sub
End If
Gongzi = InputBox("请输入要增加多少工资", "增薪", "1000")
For I = 3 To Range("c2000").End(xlUp).Row
If Range("c" & I) = Bumen Then
Range("d" & I) = Range("d" & I) + Gongzi
Range("f" & I) = Range("d" & I) + Range("e" & I)
End If
Next
MsgBox "恭喜您，指定调整" & Bumen & "增加" & Gongzi & "已经调整完毕", vbInformation, "消息"
End If
End Sub
3.减少工资代码
可参考增加代码设计。或者，使用负数减少。
