消費税計算

請求期間で計算した売上金額に0.05をかけた消費税を消費税シートに保存する。

Private Sub cmdSyouhizei_Click()
    Dim i As Long
    Dim j As Long
    Dim lastRow As Long
    Dim lastRow1 As Long
    Dim kei As Long
    Dim denno As Long
'売上データの取り出し
    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, 4)
            Worksheets("作業").Cells(j, 3) = 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, 3)).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, 3))
        .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, 3)
        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) = Worksheets("作業").Cells(i, 2)
           Worksheets("作業1").Cells(j, 3) = kei * 0.05
           j = j + 1
           kei = 0
        End If
    Next
'消費税を消費税に追加
    lastRow = Worksheets("消費税").Cells(Rows.Count, 1).End(xlUp).Row
    denno = Worksheets("消費税").Cells(lastRow, 1) + 1
    lastRow1 = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lastRow1
        Worksheets("消費税").Cells(lastRow + 1, 1) = denno
        Worksheets("消費税").Cells(lastRow + 1, 2) = txtEnd.Text
        Worksheets("消費税").Cells(lastRow + 1, 3) = Worksheets("作業1").Cells(i, 1)
        Worksheets("消費税").Cells(lastRow + 1, 4) = Worksheets("作業1").Cells(i, 2)
        Worksheets("消費税").Cells(lastRow + 1, 5) = Worksheets("作業1").Cells(i, 3)
        lastRow = lastRow + 1
        denno = denno + 1
    Next
    MsgBox "消費税データが作成されました"
    Unload Me
    Worksheets("メニュー").Select
End Sub