Top> 販売管理> 請求書計算・消費税計算

VBA通信教材申込み

VBA課題解決型実践塾セミナー内容

申込 ヤフーショッピング

請求書計算・消費税計算


請求書計算

請求書計算・消費税計算

請求書発行の期間を入力します。
(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
'得意先を集計した作業1のデータを得意先の今回売上金額に更新
  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
'得意先を集計した作業1のデータを得意先の消費税に更新
  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
'得意先を集計した作業1のデータを得意先の入金累計に更新
  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
'得意先を集計した作業1のデータを得意先の今回売上金額に更新
  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
'得意先を集計した作業1のデータを得意先の入金累計に更新
  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

消費税計算

請求書計算・消費税計算

請求期間で計算した売上金額に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

先頭へ

 

塾長あいさつ
基本方針(考え方)
通信教育内容
課題解決セミナー内容
初心者基礎コース内容
カリキュラム・料金
案内地図・各種リンク
問合せ・申込み
アベノ塾
システムの内製化
アサイコンピュータACS
応用部品集(有料)
販売管理(事例)
財務管理(事例)
顧客管理(事例)
見積もり(事例)
給与(事例)
介護(事例)
ゴースト暗算(事例)
部品集(部品の基礎)
部品集(シート関連)
部品集(データベース)
部品集(セル関連)
部品集(関数)
部品集(フォーム関連)
部品集(ブック関連)
部品集(その他)
変数の勉強
繰り返しと条件文
マスター登録.訂正.削除
フォルダー内のファイル
項目をシート名で作成
ブックの操作
CSVファイルの取り込み
呼び出し元・先
商品マスター検索
エクセル関数をVBAで
エクセル関数を使う
よくつかうプログラム
販売管理(ソース)
財務管理(ソース)
給与管理(概要・画面)
見積もり(概要・画面)
工程管理(概要・画面)
原価管理(概要・画面)