(4)合計残高試算表
科目表
合計残高表
Option Explicit
Private zandaka As Long
Private lastrow As Long
Private lastrow1 As Long
Private i As Long
Private j As Long
Private kcode As Long
Private kei As Long
Private kkubun As String
Private kisyu As String
Private kaisi As String
Private syuuryou As String
Private Sub txtkaisi_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
If Len(txtkaisi.Text) <> 10 Then
MsgBox "桁数は10桁です(2012/01/01) "
Exit Sub
End If
End If
End Sub
Private Sub txtsyuuryou_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
If Len(txtsyuuryou.Text) <> 10 Then
MsgBox "桁数は10桁です(2012/01/01) "
Exit Sub
End If
End If
End Sub
Private Sub cmdJikkou_Click()
'計算期間を記録
Worksheets("科目表").Cells(2, 12) = txtkaisi.Text
Worksheets("科目表").Cells(2, 13) = txtsyuuryou.Text
'科目表の期間前残高・借方金額・貸方金額・残高をクリア
Call kamokuclear
'作業・作業1をクリア
Call sagyouclear
'期首残高を期間前残高に更新
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Worksheets("科目表").Cells(i, 6) = Worksheets("科目表").Cells(i, 4)
Next
'開始年月日が期首年月日の場合かどうか
If txtkaisi.Text = Worksheets("メニュー").Cells(2, 2) Then
'借方の更新
Call keisanr
'作業・作業1をクリア
Call sagyouclear
'貸方の更新
Call keisans
'残高計算
Call zandakakeisan
Else
'期首~開始日まえの計算
'借方の更新
Call keisanrt
'作業・作業1をクリア
Call sagyouclear
'貸方の更新
Call keisanst
'残高計算
Call zandakakeisan
'開始日~終了日の計算
'残高を期間前残高に更新
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Worksheets("科目表").Cells(i, 6) = Worksheets("科目表").Cells(i, 9)
Next
'科目表の借方金額・貸方金額・残高をクリア
Call kamokuclear1
'作業・作業1をクリア
Call sagyouclear
'借方の更新
Call keisanr
'作業・作業1をクリア
Call sagyouclear
'貸方の更新
Call keisans
'残高計算
Call zandakakeisan
End If
Unload Me
Worksheets("科目表").Activate
End Sub
Private Sub kamokuclear()
'科目表の期間前残高・借方金額・貸方金額・残高をクリア
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
For j = 6 To 9
Worksheets("科目表").Cells(i, j) = ""
Next
Next
End Sub
Private Sub sagyouclear()
'作業・作業1をクリア
Worksheets("作業").Cells.Clear
Worksheets("作業").Cells(1, 1) = "科目コード"
Worksheets("作業").Cells(1, 2) = "金額"
Worksheets("作業1").Cells.Clear
Worksheets("作業1").Cells(1, 1) = "科目コード"
Worksheets("作業1").Cells(1, 2) = "金額"
End Sub
Private Sub kamokuclear1()
'科目表の借方金額・貸方金額・残高をクリア
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
For j = 7 To 9
Worksheets("科目表").Cells(i, j) = ""
Next
Next
End Sub
Private Sub keisanr()
'仕訳帳から期間のデータを取り出す(開始日=期首~終了日)
Call kikantoridasi(2, 4)
'コードで並び替える
Call narabikae
'同じデータを集約した別の表を作成
Call hyousakusei
'作業1の金額を科目シートに更新する
Call kamokukousin(7)
End Sub
Private Sub keisans()
'仕訳帳から期間のデータを取り出す(開始日=期首~終了日)
Call kikantoridasi(5, 7)
'コードで並び替える
Call narabikae
'同じデータを集約した別の表を作成
Call hyousakusei
'作業1の金額を科目シートに更新する
Call kamokukousin(8)
End Sub
Private Sub keisanrt()
'仕訳帳から期間のデータを取り出す(期首~開始日まえ)
Call kikantoridasit(2, 4)
'コードで並び替える
Call narabikae
'同じデータを集約した別の表を作成
Call hyousakusei
'作業1の金額を科目シートに更新する
Call kamokukousin(7)
End Sub
Private Sub keisanst()
'仕訳帳から期間のデータを取り出す
Call kikantoridasit(5, 7)
'コードで並び替える
Call narabikae
'同じデータを集約した別の表を作成
Call hyousakusei
'作業1の金額を科目シートに更新する
Call kamokukousin(8)
End Sub
Private Sub kikantoridasi(x As Long, y As Long)
'仕訳帳から期間のデータを取り出す(開始日=期首~終了日)借方
Worksheets("仕訳帳").Activate
lastrow = Worksheets("仕訳帳").Cells(Rows.Count, 1).End(xlUp).Row
j = 2
For i = 2 To lastrow
If Worksheets("仕訳帳").Cells(i, 1) >= txtkaisi.Text And Worksheets("仕訳帳").Cells(i, 1) <= txtsyuuryou.Text Then
Worksheets("作業").Cells(j, 1) = Worksheets("仕訳帳").Cells(i, x)
Worksheets("作業").Cells(j, 2) = Worksheets("仕訳帳").Cells(i, y)
j = j + 1
End If
Next
End Sub
Private Sub kikantoridasit(x As Long, y As Long)
'仕訳帳から期間のデータを取り出す(期首~開始日まえ)借方
kisyu = Worksheets("メニュー").Cells(2, 2)
kaisi = txtkaisi.Text
syuuryou = txtsyuuryou.Text
Worksheets("仕訳帳").Activate
lastrow = Worksheets("仕訳帳").Cells(Rows.Count, 1).End(xlUp).Row
j = 2
For i = 2 To lastrow
If Worksheets("仕訳帳").Cells(i, 1) >= kisyu And Worksheets("仕訳帳").Cells(i, 1) < kaisi Then
Worksheets("作業").Cells(j, 1) = Worksheets("仕訳帳").Cells(i, x)
Worksheets("作業").Cells(j, 2) = Worksheets("仕訳帳").Cells(i, y)
j = j + 1
End If
Next
End Sub
Private Sub narabikae()
'コードで並び替える
lastrow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("作業").Activate
Range(Cells(2, 1), Cells(lastrow, 2)).Select
ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(2, 1), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("作業").Sort
.SetRange Range(Cells(2, 1), Cells(lastrow, 2))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub hyousakusei()
'同じデータを集約した別の表を作成
j = 2
kei = 0
For i = 2 To lastrow
kei = kei + Worksheets("作業").Cells(i, 2)
If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
Worksheets("作業1").Cells(j, 2) = kei
j = j + 1
kei = 0
End If
Next
End Sub
Private Sub kamokukousin(x As Long)
'作業1の金額を科目シートに更新する
Worksheets("作業1").Activate
lastrow = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("科目表").Activate
lastrow1 = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
kcode = Worksheets("作業1").Cells(i, 1)
For j = 2 To lastrow1
If Worksheets("科目表").Cells(j, 1) = kcode Then
Worksheets("科目表").Cells(j, x) = Worksheets("作業1").Cells(i, 2)
Exit For
End If
Next
j = 2
Next
End Sub
Private Sub zandakakeisan()
'残高計算
Worksheets("科目表").Activate
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
kkubun = kamokukubunc(Worksheets("科目表").Cells(i, 1))
If kkubun = "流動資産" Or kkubun = "固定資産" Or kkubun = "仕入" Or kkubun = "販売管理費" Or kkubun = "営業外費用" Then
Worksheets("科目表").Cells(i, 9) = Worksheets("科目表").Cells(i, 6) + Worksheets("科目表").Cells(i, 7) - Worksheets("科目表").Cells(i, 8)
Else
Worksheets("科目表").Cells(i, 9) = Worksheets("科目表").Cells(i, 6) + Worksheets("科目表").Cells(i, 8) - Worksheets("科目表").Cells(i, 7)
End If
Next
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdkisyu_Click()
txtkaisi.Text = Worksheets("メニュー").Cells(2, 2)
End Sub
標準モジュールに記入
Function kamokukubunc(kcode As Long) As String
Dim lastrow As Long
Dim i As Long
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If kcode = Worksheets("科目表").Cells(i, 1) Then
kamokukubunc = Worksheets("科目表").Cells(i, 5)
Exit Function
End If
Next
kamokukubunc = ""
End Function
Sub 合計残高試算表()
goukeizandaka.Show
Call 合計残高
End Sub
科目表から合計残高表へ科目区分で集計しながら転記
科目表の区分が変わるタイミングで区分計を合計残高表に転記している。
Sub 合計残高()
Dim lastrow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim 流動資産(3) As Long
Dim 固定資産(3) As Long
Dim 流動負債(3) As Long
Dim 資本(3) As Long
Dim 売上(3) As Long
Dim 仕入(3) As Long
Dim 販売管理費(3) As Long
Dim 営業外収益(3) As Long
Dim 営業外費用(3) As Long
'計算期間を記録
Worksheets("合計残高").Cells(2, 9) = Worksheets("科目表").Cells(2, 12)
Worksheets("合計残高").Cells(2, 10) = Worksheets("科目表").Cells(2, 13)
'合計残高のクリア
lastrow = Worksheets("合計残高").Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lastrow
For j = 1 To 6
Worksheets("合計残高").Cells(i, j) = ""
Next
Next
'科目表から合計残高へ移行
lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row
j = 2
For i = 2 To lastrow
If Worksheets("科目表").Cells(i, 5) = Worksheets("科目表").Cells(i + 1, 5) Then
Worksheets("合計残高").Cells(j, 1) = Worksheets("科目表").Cells(i, 1)
Worksheets("合計残高").Cells(j, 2) = Worksheets("科目表").Cells(i, 2)
Worksheets("合計残高").Cells(j, 3) = Worksheets("科目表").Cells(i, 6)
Worksheets("合計残高").Cells(j, 4) = Worksheets("科目表").Cells(i, 7)
Worksheets("合計残高").Cells(j, 5) = Worksheets("科目表").Cells(i, 8)
Worksheets("合計残高").Cells(j, 6) = Worksheets("科目表").Cells(i, 9)
Select Case Worksheets("科目表").Cells(i, 5)
Case "流動資産"
For k = 0 To 3
流動資産(k) = 流動資産(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "固定資産"
For k = 0 To 3
固定資産(k) = 固定資産(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "流動負債"
For k = 0 To 3
流動負債(k) = 流動負債(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "資本"
For k = 0 To 3
資本(k) = 資本(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "売上"
For k = 0 To 3
売上(k) = 売上(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "仕入"
For k = 0 To 3
仕入(k) = 仕入(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "販売管理費"
For k = 0 To 3
販売管理費(k) = 販売管理費(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "営業外収益"
For k = 0 To 3
営業外収益(k) = 営業外収益(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "営業外費用"
For k = 0 To 3
営業外費用(k) = 営業外費用(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
End Select
j = j + 1
Else
Worksheets("合計残高").Cells(j, 1) = Worksheets("科目表").Cells(i, 1)
Worksheets("合計残高").Cells(j, 2) = Worksheets("科目表").Cells(i, 2)
Worksheets("合計残高").Cells(j, 3) = Worksheets("科目表").Cells(i, 6)
Worksheets("合計残高").Cells(j, 4) = Worksheets("科目表").Cells(i, 7)
Worksheets("合計残高").Cells(j, 5) = Worksheets("科目表").Cells(i, 8)
Worksheets("合計残高").Cells(j, 6) = Worksheets("科目表").Cells(i, 9)
Select Case Worksheets("科目表").Cells(i, 5)
Case "流動資産"
For k = 0 To 3
流動資産(k) = 流動資産(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "固定資産"
For k = 0 To 3
固定資産(k) = 固定資産(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "流動負債"
For k = 0 To 3
流動負債(k) = 流動負債(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "資本"
For k = 0 To 3
資本(k) = 資本(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "売上"
For k = 0 To 3
売上(k) = 売上(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "仕入"
For k = 0 To 3
仕入(k) = 仕入(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "販売管理費"
For k = 0 To 3
販売管理費(k) = 販売管理費(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "営業外収益"
For k = 0 To 3
営業外収益(k) = 営業外収益(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
Case "営業外費用"
For k = 0 To 3
営業外費用(k) = 営業外費用(k) + Worksheets("科目表").Cells(i, 6 + k)
Next
End Select
j = j + 1
'合計をコピー
Select Case Worksheets("科目表").Cells(i, 5)
Case "流動資産"
Worksheets("合計残高").Cells(j, 2) = "<流動資産計>"
Worksheets("合計残高").Cells(j, 3) = 流動資産(0)
Worksheets("合計残高").Cells(j, 4) = 流動資産(1)
Worksheets("合計残高").Cells(j, 5) = 流動資産(2)
Worksheets("合計残高").Cells(j, 6) = 流動資産(3)
Case "固定資産"
Worksheets("合計残高").Cells(j, 2) = "<固定資産計>"
Worksheets("合計残高").Cells(j, 3) = 固定資産(0)
Worksheets("合計残高").Cells(j, 4) = 固定資産(1)
Worksheets("合計残高").Cells(j, 5) = 固定資産(2)
Worksheets("合計残高").Cells(j, 6) = 固定資産(3)
j = j + 1
Worksheets("合計残高").Cells(j, 2) = "《資産の部》"
Worksheets("合計残高").Cells(j, 3) = 流動資産(0) + 固定資産(0)
Worksheets("合計残高").Cells(j, 4) = 流動資産(1) + 固定資産(1)
Worksheets("合計残高").Cells(j, 5) = 流動資産(2) + 固定資産(2)
Worksheets("合計残高").Cells(j, 6) = 流動資産(3) + 固定資産(3)
Case "流動負債"
Worksheets("合計残高").Cells(j, 2) = "<流動負債計>"
Worksheets("合計残高").Cells(j, 3) = 流動負債(0)
Worksheets("合計残高").Cells(j, 4) = 流動負債(1)
Worksheets("合計残高").Cells(j, 5) = 流動負債(2)
Worksheets("合計残高").Cells(j, 6) = 流動負債(3)
Case "資本"
Worksheets("合計残高").Cells(j, 2) = "<資本計>"
Worksheets("合計残高").Cells(j, 3) = 資本(0)
Worksheets("合計残高").Cells(j, 4) = 資本(1)
Worksheets("合計残高").Cells(j, 5) = 資本(2)
Worksheets("合計残高").Cells(j, 6) = 資本(3)
j = j + 1
Worksheets("合計残高").Cells(j, 2) = "《当期利益》"
Worksheets("合計残高").Cells(j, 3) = 流動資産(0) + 固定資産(0) - 流動負債(0) - 資本(0)
Worksheets("合計残高").Cells(j, 4) = 流動資産(1) + 固定資産(1) - 流動負債(1) - 資本(1)
Worksheets("合計残高").Cells(j, 5) = 流動資産(2) + 固定資産(2) - 流動負債(2) - 資本(2)
Worksheets("合計残高").Cells(j, 6) = 流動資産(3) + 固定資産(3) - 流動負債(3) - 資本(3)
j = j + 1
Worksheets("合計残高").Cells(j, 2) = "《負債・資本の部》"
Worksheets("合計残高").Cells(j, 3) = 流動負債(0) + 資本(0) + 流動資産(0) + 固定資産(0) - 流動負債(0) - 資本(0)
Worksheets("合計残高").Cells(j, 4) = 流動負債(1) + 資本(1) + 流動資産(1) + 固定資産(1) - 流動負債(1) - 資本(1)
Worksheets("合計残高").Cells(j, 5) = 流動負債(2) + 資本(2) + 流動資産(2) + 固定資産(2) - 流動負債(2) - 資本(2)
Worksheets("合計残高").Cells(j, 6) = 流動負債(3) + 資本(3) + 流動資産(3) + 固定資産(3) - 流動負債(3) - 資本(3)
Case "売上"
Worksheets("合計残高").Cells(j, 2) = "<売上計>"
Worksheets("合計残高").Cells(j, 3) = 売上(0)
Worksheets("合計残高").Cells(j, 4) = 売上(1)
Worksheets("合計残高").Cells(j, 5) = 売上(2)
Worksheets("合計残高").Cells(j, 6) = 売上(3)
Case "仕入"
Worksheets("合計残高").Cells(j, 2) = "<仕入計>"
Worksheets("合計残高").Cells(j, 3) = 仕入(0)
Worksheets("合計残高").Cells(j, 4) = 仕入(1)
Worksheets("合計残高").Cells(j, 5) = 仕入(2)
Worksheets("合計残高").Cells(j, 6) = 仕入(3)
j = j + 1
Worksheets("合計残高").Cells(j, 2) = "《売上利益》"
Worksheets("合計残高").Cells(j, 3) = 売上(0) - 仕入(0)
Worksheets("合計残高").Cells(j, 4) = 売上(1) - 仕入(1)
Worksheets("合計残高").Cells(j, 5) = 売上(2) - 仕入(2)
Worksheets("合計残高").Cells(j, 6) = 売上(3) - 仕入(3)
Case "販売管理費"
Worksheets("合計残高").Cells(j, 2) = "<販売管理費計>"
Worksheets("合計残高").Cells(j, 3) = 販売管理費(0)
Worksheets("合計残高").Cells(j, 4) = 販売管理費(1)
Worksheets("合計残高").Cells(j, 5) = 販売管理費(2)
Worksheets("合計残高").Cells(j, 6) = 販売管理費(3)
j = j + 1
Worksheets("合計残高").Cells(j, 2) = "《営業利益》"
Worksheets("合計残高").Cells(j, 3) = 売上(0) - 仕入(0) - 販売管理費(0)
Worksheets("合計残高").Cells(j, 6) = 売上(3) - 仕入(3) - 販売管理費(3)
Case "営業外収益"
Worksheets("合計残高").Cells(j, 2) = "<営業外収益計>"
Worksheets("合計残高").Cells(j, 3) = 営業外収益(0)
Worksheets("合計残高").Cells(j, 4) = 営業外収益(1)
Worksheets("合計残高").Cells(j, 5) = 営業外収益(2)
Worksheets("合計残高").Cells(j, 6) = 営業外収益(3)
Case "営業外費用"
Worksheets("合計残高").Cells(j, 2) = "<営業外費用計>"
Worksheets("合計残高").Cells(j, 3) = 営業外費用(0)
Worksheets("合計残高").Cells(j, 4) = 営業外費用(1)
Worksheets("合計残高").Cells(j, 5) = 営業外費用(2)
Worksheets("合計残高").Cells(j, 6) = 営業外費用(3)
j = j + 1
Worksheets("合計残高").Cells(j, 2) = "《当期利益》"
Worksheets("合計残高").Cells(j, 3) = 売上(0) - 仕入(0) - 販売管理費(0) + 営業外収益(0) - 営業外費用(0)
Worksheets("合計残高").Cells(j, 6) = 売上(3) - 仕入(3) - 販売管理費(3) + 営業外収益(3) - 営業外費用(3)
End Select
j = j + 1
End If
Next
Worksheets("合計残高").Activate
End Sub