（４）合計残高試算表

科目表

合計残高表

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 "桁数は１０桁です(2012/01/01) "

Exit Sub

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 "桁数は１０桁です(2012/01/01) "

Exit Sub

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)

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)

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) = ""

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) = ""

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

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

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

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

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

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

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) = ""

'科目表から合計残高へ移行

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)

For k = 0 To 3

固定資産(k) = 固定資産(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

流動負債(k) = 流動負債(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

資本(k) = 資本(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

売上(k) = 売上(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

仕入(k) = 仕入(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

販売管理費(k) = 販売管理費(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

営業外収益(k) = 営業外収益(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

営業外費用(k) = 営業外費用(k) + Worksheets("科目表").Cells(i, 6 + k)

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)

For k = 0 To 3

固定資産(k) = 固定資産(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

流動負債(k) = 流動負債(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

資本(k) = 資本(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

売上(k) = 売上(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

仕入(k) = 仕入(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

販売管理費(k) = 販売管理費(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

営業外収益(k) = 営業外収益(k) + Worksheets("科目表").Cells(i, 6 + k)

For k = 0 To 3

営業外費用(k) = 営業外費用(k) + Worksheets("科目表").Cells(i, 6 + k)

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

End Sub