【Excel VBA】2018-09-28 给指定部门增加或减少工资

案例

案例来源：Excel和Access （微信公众号）点击 - 查看原文

指定特定部门，增加或者减少工资，并汇总合计工资。

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.减少工资代码
可参考增加代码设计。或者，使用负数减少。

【Excel VBA】2018-09-28 给指定部门增加或减少工资

案例

推荐阅读更多精彩内容