請求書計算

請求書発行の期間を入力します。
(1)前回請求残高の金額を計算します。
対象期間は期首から請求発行開始日前日まで

(2)今回請求残高の金額を計算します。

・請求期間の売上金額を計算する。
・売上金額から消費税を計算する。
・請求期間の入金金額を計算する。
・得意先に今回請求金額・今回売上金額・消費税・今回入金金額を更新します。

Private Sub cmdJikkou_Click()
    Dim i As Long
    Dim j As Long
    Dim lastRow As Long
    Dim lastRow1 As Long
    Dim kei As Long
'(1)前回請求残高の計算
'売上計算
'請求開始日以前の売上データの取り出し
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        If Worksheets("売上明細").Cells(i, 2) < txtKaisi.Text Then
            Worksheets("作業").Cells(j, 1) = Worksheets("売上明細").Cells(i, 3)
            Worksheets("作業").Cells(j, 2) = Worksheets("売上明細").Cells(i, 9)
            j = j + 1
        End If
    Next
'得意先コードで並び替え
    Worksheets("作業").Select
    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 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
'得意先の前回請求残高・今回売上金額をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 12) = ""
        Worksheets("得意先").Cells(i, 13) = ""
    Next
'得意先を集計した作業１のデータを得意先の今回売上金額に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 13) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'消費税計算
'消費税から得意先コードと金額を作業に取り出す
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("消費税").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        If Worksheets("消費税").Cells(i, 2) < txtKaisi.Text Then
           Worksheets("作業").Cells(j, 1) = Worksheets("消費税").Cells(i, 3)
           Worksheets("作業").Cells(j, 2) = Worksheets("消費税").Cells(i, 5)
        End If
        j = j + 1
    Next
'得意先コードで並び替え
    Worksheets("作業").Select
    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 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
'得意先の消費税をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 14) = ""
    Next
'得意先を集計した作業１のデータを得意先の消費税に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 14) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'入金計算
'入金明細から得意先コードと金額を作業に取り出す
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        If Worksheets("入金明細").Cells(i, 2) < txtKaisi.Text Then
            Worksheets("作業").Cells(j, 1) = Worksheets("入金明細").Cells(i, 3)
            Worksheets("作業").Cells(j, 2) = Worksheets("入金明細").Cells(i, 6)
            j = j + 1
        End If
    Next
'得意先コードで並び替え
    Worksheets("作業").Select
    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 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
'得意先の入金累計をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 15) = ""
    Next
'得意先を集計した作業１のデータを得意先の入金累計に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 15) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'得意先前回請求残高=期首残高+開始日まで売上+開始日まで消費税-開始日まで入金
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 12) = Worksheets("得意先").Cells(i, 7) + Worksheets("得意先").Cells(i, 13) + Worksheets("得意先").Cells(i, 14) - Worksheets("得意先").Cells(i, 15)
        Worksheets("得意先").Cells(i, 13) = ""
        Worksheets("得意先").Cells(i, 14) = ""
        Worksheets("得意先").Cells(i, 15) = ""
    Next
'(2)今回請求残高の計算
'売上計算
'今回請求期間の売上データの取り出し
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("売上明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        If Worksheets("売上明細").Cells(i, 2) >= txtKaisi.Text And Worksheets("売上明細").Cells(i, 2) <= txtEnd.Text Then
           Worksheets("作業").Cells(j, 1) = Worksheets("売上明細").Cells(i, 3)
           Worksheets("作業").Cells(j, 2) = Worksheets("売上明細").Cells(i, 9)
           j = j + 1
        End If
    Next
'得意先コードで並び替え
    Worksheets("作業").Select
    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 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
'得意先の今回売上金額・消費税をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 13) = ""
        Worksheets("得意先").Cells(i, 14) = ""
    Next
'得意先を集計した作業１のデータを得意先の今回売上金額に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 13) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'消費税計算・消費税＝今回売上金額*0.05
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 14) = Worksheets("得意先").Cells(i, 13) * 0.05
    Next
'入金計算
'入金明細から得意先コードと金額を作業に取り出す
    Worksheets("作業").Cells.Clear
    lastRow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    For i = 2 To lastRow
        If Worksheets("入金明細").Cells(i, 2) >= txtKaisi.Text And Worksheets("入金明細").Cells(i, 2) <= txtEnd.Text Then
           Worksheets("作業").Cells(j, 1) = Worksheets("入金明細").Cells(i, 3)
           Worksheets("作業").Cells(j, 2) = Worksheets("入金明細").Cells(i, 6)
           j = j + 1
        End If
    Next
'得意先コードで並び替え
    Worksheets("作業").Select
    lastRow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(1, 1), Cells(lastRow, 2)).Select
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("作業").Sort.SortFields.Add Key:=Cells(1, 1), SortOn _
    :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("作業").Sort
    .SetRange Range(Cells(1, 1), Cells(lastRow, 2))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
'得意先コードで集計
    Worksheets("作業1").Cells.Clear
    j = 1
    kei = 0
    For i = 1 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
'得意先の入金累計をクリア
    lastRow = Worksheets("得意先").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 15) = ""
    Next
'得意先を集計した作業１のデータを得意先の入金累計に更新
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        For j = 2 To lastRow
            If Worksheets("作業1").Cells(i, 1) = Worksheets("得意先").Cells(j, 1) Then
               Worksheets("得意先").Cells(j, 15) = Worksheets("作業1").Cells(i, 2)
               Exit For
            End If
        Next
    Next
'得意先今回請求残高=前回残高+請求期間売上+消費税-請求期間入金
    For i = 2 To lastRow
        Worksheets("得意先").Cells(i, 16) = Worksheets("得意先").Cells(i, 12) + Worksheets("得意先").Cells(i, 13) + Worksheets("得意先").Cells(i, 14) - Worksheets("得意先").Cells(i, 15)
    Next
    MsgBox "残高計算が終わりました"
    Unload Me
    Worksheets("メニュー").Select
End Sub