簡體中文 宏
繁體中文 巨集

文章目錄

• • • • • 工資表、工資條
        • 個稅計算
        • 提取表名
        • 根據模板創建報表
        • 多表匯總數據
        • 雙重循環處理多個 sheet

工資表、工資條

Sub gzb()Dim i As IntegerFor i = 3 To 50If Range("A" & i) = "" ThenExit ForEnd IfRange("A" & i).SelectSelection.EntireRow.DeleteNextEnd SubSub gzt()Dim i As IntegerFor i = 3 To 50 Step 2If Range("A" & i) = "" ThenExit ForEnd IfRows("1:1").SelectApplication.CutCopyMode = FalseSelection.CopyRange("A" & i).SelectSelection.Insert Shift:=xlDownNextEnd Sub

個稅計算

Sub gs()Dim i As IntegerFor i = 2 To 100If Range("a" & i) = "" ThenExit ForEnd IfIf Range("c" & i) <= 1500 ThenRange("d" & i) = Range("c" & i) * 0.03ElseIf Range("c" & i) >= 1500 And Range("c" & i) < 4500 ThenRange("d" & i) = Range("c" & i) * 0.1 - 105ElseIf Range("c" & i) >= 4500 And Range("c" & i) < 9000 ThenRange("d" & i) = Range("c" & i) * 0.2 - 555ElseIf Range("c" & i) >= 9000 And Range("c" & i) < 35000 ThenRange("d" & i) = Range("c" & i) * 0.25 - 1005ElseIf Range("c" & i) >= 35000 And Range("c" & i) < 55000 ThenRange("d" & i) = Range("c" & i) * 0.3 - 2755ElseIf Range("c" & i) >= 55000 And Range("c" & i) < 80000 ThenRange("d" & i) = Range("c" & i) * 0.35 - 5505ElseIf Range("c" & i) >= 80000 ThenRange("d" & i) = Range("c" & i) * 0.45 - 13505End IfNextEnd Sub

提取表名

Sub tq()Dim i As IntegerFor i = 2 To Sheets.CountRange("a" & i - 1) = Sheets(i).NameNextEnd Sub

根據模板創建報表

Sub test() Dim i As IntegerFor i = 1 To 31Sheet1.Copy after:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = "5月" & i & "日"Sheets(Sheets.Count).Range("e5") = "2016-5-" & i NextEnd Sub

多表匯總數據

Sub hz() Dim i As IntegerFor i = 2 To Sheets.Count'Sheet1.Range("b10") = Sheets(i).Range("e5")Sheet1.Range("b" & i + 8) = Sheets(i).Range("e5")Sheet1.Range("c" & i + 8) = Sheets(i).Range("e6")Sheet1.Range("d" & i + 8) = Sheets(i).Range("e44")NextEnd Sub

雙重循環處理多個 sheet

Sub test() Dim i As Integer Dim j As IntegerFor j = 1 To Sheets.CountFor i = 100 To 2 Step -1If Sheets(j).Range("b" & i) = "瞶" ThenSheets(j).Range("c" & i) = "LG"ElseIf Sheets(j).Range("b" & i) = "ゅ" ThenSheets(j).Range("c" & i) = "WK"ElseSheets(j).Range("c" & i) = "CJ"End IfIf Sheets(j).Range("e" & i) = "╧" ThenSheets(j).Range("f" & i) = "ネ"ElseIf Sheets(j).Range("e" & i) = "" ThenSheets(j).Range("f" & i) = ""End IfIf Sheets(j).Range("d" & i) = "" ThenSheets(j).Range("d" & i).EntireRow.DeleteEnd IfNext NextEnd Sub

總結

以上是生活随笔為你收集整理的生成工资条 恢复工资表 宏 巨集 vba的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。