ビジネスに役立つ講座や交流会を開催
社長・個人事業主からサラリーマン・主婦まで、どなたでも
アベノ塾

ホーム  会場案内  お問合せ

〒545-0052
大阪市阿倍野区阿倍野筋3-12-2
あべのクオレ1F
(ナガセキャリアプラザ アベノ校)
TEL 06-6647-5571

入金処理

入金伝票で入力したデータを入金明細シートに登録します。
・入金伝票Noは入金明細シートの最後の行の伝票Noに1を加算します。
・得意先コードにコード番号を入力すれば得意先名が表示されます。
・得意先コード入力にカーソルがある状態で得意先コードボタンをクリックすると
得意先検索のリストボックスが表示されます。

・入金方法によって明細に登録します。

 

 

 

 

 

 

 

 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim m得意先名 As String
    Dim m得意先cd As Long
    With Target
'得意先コードの入力
        If .Row = 4 And .Column = 2 Then
           If Cells(4, 2) = "" Then
              Exit Sub
           End If
           m得意先cd = Cells(4, 2)
           m得意先名 = tkensaku(m得意先cd)
           Cells(5, 2) = m得意先名
        End If
        If (.Row = 7 And .Column = 2) Or (.Row = 7 And .Column = 3) Or (.Row = 7 And .Column = 4) Or (.Row = 7 And .Column = 5) Then
            Cells(7, 6) = Cells(7, 2) + Cells(7, 3) + Cells(7, 4) + Cells(7, 5)
        End If
    End With
End Sub
Sub 入金伝票登録()
    Dim lastrow As Long
    Dim gyou As Long
    lastrow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    gyou = lastrow + 1
'入金区分
    If Cells(7, 2) <> "" Then
        Worksheets("入金明細").Cells(gyou, 1) = Cells(3, 5)
        Worksheets("入金明細").Cells(gyou, 2) = Cells(3, 2)
        Worksheets("入金明細").Cells(gyou, 3) = Cells(4, 2)
        Worksheets("入金明細").Cells(gyou, 4) = Cells(5, 2)
        Worksheets("入金明細").Cells(gyou, 5) = Cells(6, 2)
        Worksheets("入金明細").Cells(gyou, 6) = Cells(7, 2)
        gyou = gyou + 1
    End If
    If Cells(7, 3) <> "" Then
        Worksheets("入金明細").Cells(gyou, 1) = Cells(3, 5)
        Worksheets("入金明細").Cells(gyou, 2) = Cells(3, 2)
        Worksheets("入金明細").Cells(gyou, 3) = Cells(4, 2)
        Worksheets("入金明細").Cells(gyou, 4) = Cells(5, 2)
        Worksheets("入金明細").Cells(gyou, 5) = Cells(6, 3)
        Worksheets("入金明細").Cells(gyou, 6) = Cells(7, 3)
        gyou = gyou + 1
    End If
    If Cells(7, 4) <> "" Then
        Worksheets("入金明細").Cells(gyou, 1) = Cells(3, 5)
        Worksheets("入金明細").Cells(gyou, 2) = Cells(3, 2)
        Worksheets("入金明細").Cells(gyou, 3) = Cells(4, 2)
        Worksheets("入金明細").Cells(gyou, 4) = Cells(5, 2)
        Worksheets("入金明細").Cells(gyou, 5) = Cells(6, 4)
        Worksheets("入金明細").Cells(gyou, 6) = Cells(7, 4)
        gyou = gyou + 1
    End If
    If Cells(7, 5) <> "" Then
        Worksheets("入金明細").Cells(gyou, 1) = Cells(3, 5)
        Worksheets("入金明細").Cells(gyou, 2) = Cells(3, 2)
        Worksheets("入金明細").Cells(gyou, 3) = Cells(4, 2)
        Worksheets("入金明細").Cells(gyou, 4) = Cells(5, 2)
        Worksheets("入金明細").Cells(gyou, 5) = Cells(6, 5)
        Worksheets("入金明細").Cells(gyou, 6) = Cells(7, 5)
        gyou = gyou + 1
    End If
    Call 入金伝票クリア
End Sub
Sub 入金伝票クリア()
    Dim lastrow As Long
    lastrow = Worksheets("入金明細").Cells(Rows.Count, 1).End(xlUp).Row
    Cells(3, 2) = ""
    Cells(4, 2) = ""
    Cells(5, 2) = ""
    Cells(7, 2) = ""
    Cells(7, 3) = ""
    Cells(7, 4) = ""
    Cells(7, 5) = ""
    Cells(7, 6) = ""
    Cells(3, 5) = Worksheets("入金明細").Cells(lastrow, 1) + 1
End Sub
Sub 入金伝票メニュー()
    Cells(3, 2) = ""
    Cells(3, 5) = ""
    Cells(4, 2) = ""
    Cells(5, 2) = ""
    Cells(7, 2) = ""
    Cells(7, 3) = ""
    Cells(7, 4) = ""
    Cells(7, 5) = ""
    Cells(7, 6) = ""
    Worksheets("メニュー").Select
End Sub