1．給与入力
１）給与データ作成
社員から取り出す項目
基本項目
社員コード・社員名
支給項目
基本給・家族手当・通勤手当
控除項目
健康保険料・厚生年金・雇用保険料・住民税
同じ月のデータがダブルとデータがおかしくなるのでダブリを防いでいる。
Private Sub cmdJikkou_Click()
Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim lastrow1 As Long
lastrow = Worksheets("社員").Cells(Rows.Count, 1).End(xlUp).Row
lastrow1 = Worksheets("給与台帳").Cells(Rows.Count, 1).End(xlUp).Row
j = 1
'ダブリチェック
For i = 2 To lastrow1
If Worksheets("給与台帳").Cells(i, 1) = lsttuki.Text Then
MsgBox "選択された" & lsttuki.Text & "のデータは作成済みです"
Exit Sub
End If
Next
For i = 2 To lastrow
'基本項目
Worksheets("給与台帳").Cells(lastrow1 + j, 1) = lsttuki.Text
Worksheets("給与台帳").Cells(lastrow1 + j, 2) = Worksheets("社員").Cells(i, 1)
Worksheets("給与台帳").Cells(lastrow1 + j, 3) = Worksheets("社員").Cells(i, 2)
'支給項目
Worksheets("給与台帳").Cells(lastrow1 + j, 4) = Worksheets("社員").Cells(i, 3)
Worksheets("給与台帳").Cells(lastrow1 + j, 6) = Worksheets("社員").Cells(i, 4)
Worksheets("給与台帳").Cells(lastrow1 + j, 7) = Worksheets("社員").Cells(i, 5)
'控除項目
Worksheets("給与台帳").Cells(lastrow1 + j, 11) = Worksheets("社員").Cells(i, 6)
Worksheets("給与台帳").Cells(lastrow1 + j, 12) = Worksheets("社員").Cells(i, 7)
Worksheets("給与台帳").Cells(lastrow1 + j, 13) = Worksheets("社員").Cells(i, 8)
Worksheets("給与台帳").Cells(lastrow1 + j, 15) = Worksheets("社員").Cells(i, 9)
j = j + 1
Next
Unload Me
End Sub
給与処理
毎月の変動項目
支給項目
基本給・家族手当・通勤手当
控除項目
控除1・控除2
を入力する為に基本データを明細書シートに表示させます。
給与台帳から月と社員コードのデータを取り出します。
毎月個人給与入力フォームに月と社員を表示します
Private Sub UserForm_Initialize()
Dim i As Long
Dim lastrow As Long
lastrow = Worksheets("社員").Cells(Rows.Count, 1).End(xlUp).Row
With lsttuki
.AddItem "1月"
.AddItem "2月"
.AddItem "3月"
.AddItem "4月"
.AddItem "5月"
.AddItem "6月"
.AddItem "7月"
.AddItem "8月"
.AddItem "9月"
.AddItem "10月"
.AddItem "11月"
.AddItem "12月"
End With
lstSyain.ColumnCount = 2
For i = 2 To lastrow
With lstSyain
.AddItem
.List(i - 2, 0) = Worksheets("社員").Cells(i, 1)
.List(i - 2, 1) = Worksheets("社員").Cells(i, 2)
End With
Next
End Sub
給与台帳から月と社員コードのデータを明細書シートにコピーするプログラム。
Private Sub cmdJikkou_Click()
Dim i As Long
Dim lastrow As Long
lastrow = Worksheets("給与台帳").Cells(Rows.Count, 1).End(xlUp).Row
'明細書をクリアする
'コード・名前
Worksheets("明細書").Cells(2, 4) = ""
Worksheets("明細書").Cells(2, 5) = ""
'支給項目
Worksheets("明細書").Cells(10, 3) = ""
Worksheets("明細書").Cells(10, 6) = ""
Worksheets("明細書").Cells(10, 7) = ""
Worksheets("明細書").Cells(10, 8) = ""
Worksheets("明細書").Cells(12, 10) = ""
Worksheets("明細書").Cells(12, 11) = ""
'控除項目
Worksheets("明細書").Cells(15, 3) = ""
Worksheets("明細書").Cells(15, 4) = ""
Worksheets("明細書").Cells(15, 6) = ""
Worksheets("明細書").Cells(15, 7) = ""
Worksheets("明細書").Cells(15, 8) = ""
Worksheets("明細書").Cells(15, 10) = ""
Worksheets("明細書").Cells(15, 11) = ""
Worksheets("明細書").Cells(19, 5) = ""
Worksheets("明細書").Cells(19, 6) = ""
Worksheets("明細書").Cells(19, 7) = ""
Worksheets("明細書").Cells(1, 12) = lsttuki.Text
Worksheets("明細書").Cells(2, 11) = "平成26年" & lsttuki.Text & "分給与"
For i = 2 To lastrow
If Worksheets("給与台帳").Cells(i, 1) = lsttuki.Text And Worksheets("給与台帳").Cells(i, 2) = lstSyain.Text Then
Worksheets("明細書").Cells(2, 4) = Worksheets("給与台帳").Cells(i, 2)
Worksheets("明細書").Cells(2, 5) = Worksheets("給与台帳").Cells(i, 3)
'支給項目
Worksheets("明細書").Cells(10, 3) = Worksheets("給与台帳").Cells(i, 4)
Worksheets("明細書").Cells(10, 6) = Worksheets("給与台帳").Cells(i, 6)
Worksheets("明細書").Cells(12, 10) = Worksheets("給与台帳").Cells(i, 7)
'控除項目
Worksheets("明細書").Cells(15, 3) = Worksheets("給与台帳").Cells(i, 11)
Worksheets("明細書").Cells(15, 4) = Worksheets("給与台帳").Cells(i, 12)
Worksheets("明細書").Cells(15, 6) = Worksheets("給与台帳").Cells(i, 13)
Worksheets("明細書").Cells(15, 8) = Worksheets("給与台帳").Cells(i, 15)
End If
Next
Worksheets("明細書").Select
Unload Me
End Sub
