- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 4# yuan5447
是這樣嗎?- Option Explicit
- Sub Ex()
- Dim PrintMonTh As String, Rng(1 To 2) As Range, i As Integer, ii As Integer
- PrintMonTh = Format(DateAdd("M", -1, Date), "M月") '上一月份
- PrintMonTh = InputBox("請輸入月份", , PrintMonTh) '輸入正確月份
- If Val(PrintMonTh) <= 0 Or Mid(PrintMonTh, Len(PrintMonTh)) <> "月" Then GoTo ER '
- 'Val(PrintMonTh) <= 0 : 數字>0 , Mid(PrintMonTh, Len(PrintMonTh)) <> "月":最後的字元<>"月"
- On Error GoTo ER '輸入的月份工作表不存在,下面的程式碼會有錯誤
- With Sheets("薪資單列印")
- Set Rng(1) = .Range("C4:C21") '第一筆輸入資料的範圍
- .PageSetup.PrintArea = .Range("a4:k21").Address '印列範圍的設定
- End With
- With Sheets(PrintMonTh) ' . 為這物件(工作表)下的物件,方法,屬性
- i = 5
- ii = 0
- Do While .Cells(i, "A") <> "" '這物件(工作表)下的A欄<>""
- If .Cells(i, "A") <> "管理" Then
- Set Rng(2) = Rng(1).Offset(, ii * 4) 'ii * 4 : Rng(1)位移的欄數,移到下一筆要輸入的位置
- Rng(2).Cells(1) = PrintMonTh '月份
- Rng(2).Cells(2) = .Cells(i, "B") '姓名
- Rng(2).Cells(3) = .Cells(i, "P") '本奉
- Rng(2).Cells(4) = .Cells(i, "Q") '日新
- Rng(2).Cells(5) = .Cells(i, "C") '天數(出勤日數)
- Rng(2).Cells(6) = .Cells(i, "Q") * .Cells(i, "C")
- '小計 (天數*日新)
- Rng(2).Cells(7) = .Cells(i, "R") '工作津貼
- Rng(2).Cells(8) = .Cells(i, "S") '證照費
- Rng(2).Cells(9) = .Cells(i, "U") '伙食津貼
- Rng(2).Cells(10) = .Cells(i, "T") '加班費
- Rng(2).Cells(11) = .Cells(i, "L") '節慶獎金
- Rng(2).Cells(12) = .Cells(i, "V") '請假扣款
- Rng(2).Cells(13) = .Cells(i, "W") '遲到扣款
- Rng(2).Cells(14) = .Cells(i, "X") '勞保費
- Rng(2).Cells(15) = .Cells(i, "T") '健保費
- Rng(2).Cells(16) = .Cells(i, "Z") '勞退金
-
- Rng(2).Cells(18) = .Cells(i, "AB") '實領金額
- If ii = 2 Then '達 3人次
- Sheets("薪資單列印").PrintOut
- For ii = 0 To 2 '清除資料
- Rng(1).Offset(, ii * 4) = ""
- Next
- ii = 0 '次數:歸零
- Else
- ii = ii + 1
- End If
- End If
- i = i + 1
- Loop
- End With
- MsgBox "印列 完畢"
- Exit Sub
- ER:
- MsgBox "沒有 " & IIf(PrintMonTh = "", "輸入月份", PrintMonTh & " 工作表")
- End Sub
|
|
發表於 2014-4-12 06:14
| 