麻辣家族討論版版 - Powered by Discuz! Board

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

複製代碼