（13）得意先元帳

得意先元帳を発行したい期間と得意先を選択します。

標準モジュールに記述
Sub 得意先元帳()
    frmTmoto.Show
End Sub

フォームモジュールに記述
Private Sub cmdJikkou_Click()
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim lastRow As Long
    Dim lastRow1 As Long
    Dim 期首金額 As Long
    Dim 売上 As Long
    Dim 消費税 As Long
    Dim 入金 As Long
    Dim 残高 As Long
'得意先元帳クリア
    Worksheets("得意先元帳").Cells(1, 6) = ""
    Worksheets("得意先元帳").Cells(2, 6) = ""
    Worksheets("得意先元帳").Cells(2, 3) = ""
    Worksheets("得意先元帳").Cells(2, 4) = ""
    Worksheets("得意先元帳").Cells(2, 4) = ""
    lastRow = Worksheets("得意先元帳").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To lastRow
        For j = 1 To 9
            Worksheets("得意先元帳").Cells(i, j) = ""
        Next
    Next

'開始日の残高計算
'期首金額
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("得意先").Cells(i, 1) = txtTcode.Text Then
           期首金額 = Worksheets("得意先").Cells(i, 7)
        End If
    Next

'開始日以前の売上の計算
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("売上明細").Cells(i, 2) < txtKaisi.Text And Worksheets("売上明細").Cells(i, 3) = txtTcode.Text Then
           売上 = 売上 + Worksheets("売上明細").Cells(i, 9)
        End If
    Next
'開始日以前の消費税の計算
    lastRow = Worksheets("消費税").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("消費税").Cells(i, 2) < txtKaisi.Text And Worksheets("消費税").Cells(i, 3) = txtTcode.Text Then
           消費税 = 消費税 + Worksheets("消費税").Cells(i, 5)
        End If
    Next
'開始日以前の入金の計算
    lastRow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("入金明細").Cells(i, 2) < txtKaisi.Text And Worksheets("入金明細").Cells(i, 3) = txtTcode.Text Then
           入金 = 入金 + Worksheets("入金明細").Cells(i, 6)
        End If
    Next
    残高 = 期首金額 + 売上 + 消費税 - 入金
'入力項目等の表示
    Worksheets("得意先元帳").Cells(1, 6) = txtKaisi.Text
    Worksheets("得意先元帳").Cells(2, 6) = txtEnd.Text
    Worksheets("得意先元帳").Cells(2, 3) = txtTcode.Text
    Worksheets("得意先元帳").Cells(2, 4) = lblTname.Caption
    Worksheets("得意先元帳").Cells(4, 4) = "繰越金額"
    Worksheets("得意先元帳").Cells(4, 9) = 残高
'明細項目の表示
'作業のクリア
    Worksheets("作業").Cells.Clear
    Worksheets("作業").Cells(1, 1) = "売上伝票No"
    Worksheets("作業").Cells(1, 2) = "売上・入金日"
    Worksheets("作業").Cells(1, 3) = "商品コード"
    Worksheets("作業").Cells(1, 4) = "商品名(入金方法）"
    Worksheets("作業").Cells(1, 5) = "数量"
    Worksheets("作業").Cells(1, 6) = "販売単価"
    Worksheets("作業").Cells(1, 7) = "売上金額"
    Worksheets("作業").Cells(1, 8) = "入金金額"
    Worksheets("作業").Cells(1, 9) = "残高"

'売上データの取り出し
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 2
    For i = 2 To lastRow
        If Worksheets("売上明細").Cells(i, 2) >= txtKaisi.Text And Worksheets("売上明細").Cells(i, 2) <= txtEnd.Text And Worksheets("売上明細").Cells(i, 3) = txtTcode.Text Then
            Worksheets("作業").Cells(j, 1) = Worksheets("売上明細").Cells(i, 1)
            Worksheets("作業").Cells(j, 2) = Worksheets("売上明細").Cells(i, 2)
            Worksheets("作業").Cells(j, 3) = Worksheets("売上明細").Cells(i, 5)
            Worksheets("作業").Cells(j, 4) = Worksheets("売上明細").Cells(i, 6)
            Worksheets("作業").Cells(j, 5) = Worksheets("売上明細").Cells(i, 7)
            Worksheets("作業").Cells(j, 6) = Worksheets("売上明細").Cells(i, 8)
            Worksheets("作業").Cells(j, 7) = Worksheets("売上明細").Cells(i, 9)
            j = j + 1
        End If
    Next
'消費税データの取り出し
    lastRow = Worksheets("消費税").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("消費税").Cells(i, 2) >= txtKaisi.Text And Worksheets("消費税").Cells(i, 2) <= txtEnd.Text And Worksheets("消費税").Cells(i, 3) = txtTcode.Text Then
            Worksheets("作業").Cells(j, 1) = Worksheets("消費税").Cells(i, 1)
            Worksheets("作業").Cells(j, 2) = Worksheets("消費税").Cells(i, 2)
            Worksheets("作業").Cells(j, 4) = "消費税"
            Worksheets("作業").Cells(j, 7) = Worksheets("消費税").Cells(i, 5)
            j = j + 1
        End If
    Next
'入金明細データの取り出し
    lastRow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("入金明細").Cells(i, 2) >= txtKaisi.Text And Worksheets("入金明細").Cells(i, 2) <= txtEnd.Text And Worksheets("入金明細").Cells(i, 3) = txtTcode.Text Then
            Worksheets("作業").Cells(j, 1) = Worksheets("入金明細").Cells(i, 1)
            Worksheets("作業").Cells(j, 2) = Worksheets("入金明細").Cells(i, 2)
            Worksheets("作業").Cells(j, 4) = Worksheets("入金明細").Cells(i, 5)
            Worksheets("作業").Cells(j, 8) = Worksheets("入金明細").Cells(i, 6)
            j = j + 1
        End If
    Next
'作業データの並び替え
    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(2, 1), Cells(lastRow, 8)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(2, 2), SortOn _
        :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
        .SetRange Range(Cells(2, 1), Cells(lastRow, 8))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'残高計算
    For i = 2 To lastRow
        Worksheets("作業").Cells(i, 9) = 残高 + Worksheets("作業").Cells(i, 7) - Worksheets("作業").Cells(i, 8)
        残高 = Worksheets("作業").Cells(i, 9)
    Next
'得意先元帳に転記
    j = 5
    For i = 2 To lastRow
        For k = 1 To 9
            Worksheets("得意先元帳").Cells(j, k) = Worksheets("作業").Cells(i, k)
        Next
        j = j + 1
    Next

    Worksheets("得意先元帳").Select
    Unload Me
End Sub
Private Sub cmdCancel_Click()
    Unload Me
End Sub
Private Sub UserForm_Initialize()
    Dim lastRow As Long
    Dim i As Long
    Dim hajime As String
    Dim saigo As String
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    lstTokui.ColumnCount = 2
    For i = 2 To lastRow
        With lstTokui
            .AddItem
            .List(i - 2, 0) = Worksheets("得意先").Cells(i, 1)
            .List(i - 2, 1) = Worksheets("得意先").Cells(i, 2)
        End With
    Next
'売上明細の始め（期首）と最後（今）を表示する
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    hajime = Worksheets("売上明細").Cells(2, 2)
    saigo = Worksheets("売上明細").Cells(2, 2)
    For i = 2 To lastRow
        If Worksheets("売上明細").Cells(i, 2) < hajime Then
           hajime = Worksheets("売上明細").Cells(i, 2)
        End If
        If Worksheets("売上明細").Cells(i, 2) > saigo Then
           saigo = Worksheets("売上明細").Cells(i, 2)
        End If
    Next
'消費税の最後をチェック
    lastRow = Worksheets("消費税").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("消費税").Cells(i, 2) > saigo Then
           saigo = Worksheets("消費税").Cells(i, 2)
        End If
    Next
'消費税の最後をチェック
    lastRow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        If Worksheets("入金明細").Cells(i, 2) > saigo Then
           saigo = Worksheets("入金明細").Cells(i, 2)
        End If
    Next
    txtKaisi.Text = hajime
    txtEnd.Text = saigo
End Sub
Private Sub lstTokui_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    txtTcode = lstTokui.Text
    lblTname = lstTokui.List(lstTokui.ListIndex, 1)
End Sub