Top > 販売管理> 得意先元帳

VBA通信教材申込み

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

申込 ヤフーショッピング

得意先元帳


(11)得意先元帳

得意先元帳

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

(1)残高計算

得意先元帳

(2)明細データの取り出し

得意先元帳

(3)残高計算と得意先元帳への転記

得意先元帳

得意先元帳

得意先元帳画面

得意先元帳

標準モジュールに記述
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) = ""
  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

 

 

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