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

ホーム  会場案内  お問合せ

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

(2)シートを使った仕訳伝票

エクセルで振替伝票を作成した場合は関数がシートに記入されているため間違って消してしまうことがあります。

VBAで作ったシートを使った仕訳伝票は計算式が記入されていませんので安心して使うことができます。


a)科目コードを直接入力した場合の検索

1行目の借方コードで100を入力してエンターキーを押すと借方科目に現金が表示されます。

VLOOKUP関数を使えばこのようになりますが、科目が追加された時は範囲を変更しないといけません。

=VLOOKUP(B5,科目表!$A$1:$B$45,2)

VBAを使う場合2つの操作が必要になります。

1つはコード入力の範囲が変更になったとき(科目コードを入力した時)

5行2列から9行2列・5行5列から9行5列までが変化したとき

Target.Column = 2 And Target.Row >= 5 And Target.Row <= 9

Target.Column = 5 And Target.Row >= 5 And Target.Row <= 9

の条件があったときに検索処理に入ります。

Targetの使い方と仕訳伝票シートにVBAが書かれていることがポイントです。

金額の合計式もありますので理解してください。

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim x As Long

    Dim i As Long

    Dim goukei As Long

    If Target.Column = 2 And Target.Row >= 5 And Target.Row <= 9 Then

        x = Target.Row

        Cells(x, 3) = kamokukensakuf(Cells(x, 2))

    End If

    If Target.Column = 5 And Target.Row >= 5 And Target.Row <= 9 Then

        x = Target.Row

        Cells(x, 6) = kamokukensakuf(Cells(x, 5))

    End If

    If Target.Column = 4 And Target.Row >= 5 And Target.Row <= 9 Then

        x = Target.Row

        For i = 1 To 5

            goukei = goukei + Cells(i + 4, 4)

        Next

        Cells(10, 4) = goukei

    End If

End Sub

2つめは科目コードを引数にして科目名を戻り値とする関数kamokukensakufの使い方です。

これは標準モジュールに書きます。

検索ボタンでも使うモジュールです。

Function kamokukensakuf(kcode As Long) As String

         Dim lastrow As Long

         Dim i As Long

         lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row

         For i = 2 To lastrow

             If kcode = Worksheets("科目表").Cells(i, 1) Then

                kamokukensakuf = Worksheets("科目表").Cells(i, 2)

                Exit Function

             End If

         Next

         kamokukensakuf = ""

         MsgBox "科目コードがみつかりません"

End Function


(b)科目一覧表からの検索

HelpWindowに科目表を持ってくるところは

        With kamokulist

            .AddItem

            .List(i - 2, 0) = Worksheets("科目表").Cells(i, 1)

            .List(i - 2, 1) = Worksheets("科目表").Cells(i, 2)

        End With

ポイントは科目コードを入力するセルがアクティブであることが必要です。

科目コード・科目名をアクティブとアクティブの右隣

Cells(ActiveCell.Row, ActiveCell.Column + 1) =

kamokulist.List(kamokulist.ListIndex, 1)

に代入しています。科目名はListIndexを使っています。

実行ボタンをクリックするより科目名でダブルクリックで取り込む方が自然です。

その後Unload Meでフォームを閉じています

Private Sub cmdCancel_Click()

    Unload Me

End Sub

Private Sub cmdJikkou_Click()

    ActiveCell = kamokulist.Text

    Cells(ActiveCell.Row, ActiveCell.Column + 1) =

kamokulist.List(kamokulist.ListIndex, 1)

End Sub

Private Sub UserForm_Initialize()

    Dim i As Long

    Dim lastrow As Long

    lastrow = Worksheets("科目表").Cells(Rows.Count, 1).End(xlUp).Row

    kamokulist.ColumnCount = 2

    For i = 2 To lastrow

        With kamokulist

            .AddItem

            .List(i - 2, 0) = Worksheets("科目表").Cells(i, 1)

            .List(i - 2, 1) = Worksheets("科目表").Cells(i, 2)

        End With

    Next

End Sub

Private Sub kamokulist_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    ActiveCell = kamokulist.Text

    Unload Me

End Sub


(c)伝票登録

コンピュータの効果は大量のデータを加工することです。

仕訳伝票シートで入力したデータを仕訳帳のシートにためていかないと意味がないです。

仕訳帳に追加するポイントは仕訳帳の最後の行を調べてそれに1を+した行数に代入することです。

5行の仕訳伝票のなかでいつも5行使うとは限りませんので空白行の上の行まで追加する処理を入れます。

        If Worksheets("仕訳伝票").Cells(i, 2) <> "" Then

 

 

Sub 仕訳登録()

    Dim lastrow As Long

    Dim i As Long

    Dim j As Long

    Dim aaa As String

'仕訳帳コードの最終行の取り出し

    j = Worksheets("仕訳帳").Cells(Rows.Count, 1).End(xlUp).Row

'仕訳入力の最終行までの繰り返し

    For i = 5 To 9

'現金出納帳コードの入金金額があるか空白でない(入金データの処理)

        If Worksheets("仕訳伝票").Cells(i, 2) <> "" Then

'仕訳入力から仕訳帳コードへ日付の移行

           Worksheets("仕訳帳").Cells(j + 1, 1) = Worksheets("仕訳伝票").Cells(3, 6)

'借方の科目コード

           Worksheets("仕訳帳").Cells(j + 1, 2) = Worksheets("仕訳伝票").Cells(i, 2)

'借方の科目名

           Worksheets("仕訳帳").Cells(j + 1, 3) = Worksheets("仕訳伝票").Cells(i, 3)

'借方金額

           Worksheets("仕訳帳").Cells(j + 1, 4) = Worksheets("仕訳伝票").Cells(i, 4)

'貸方の科目コード

           Worksheets("仕訳帳").Cells(j + 1, 5) = Worksheets("仕訳伝票").Cells(i, 5)

'貸方の科目名

           Worksheets("仕訳帳").Cells(j + 1, 6) = Worksheets("仕訳伝票").Cells(i, 6)

'貸方金額

           Worksheets("仕訳帳").Cells(j + 1, 7) = Worksheets("仕訳伝票").Cells(i, 4)

'仕訳入力から仕訳帳コードへ摘要の移行

           Worksheets("仕訳帳").Cells(j + 1, 8) = Worksheets("仕訳伝票").Cells(i, 7)

        End If

'仕訳帳コードの行Noの1加算

        j = j + 1

    Next

'入力データのクリア(コードのクリアに注意)

    For i = 5 To 9

        For j = 2 To 7

            Worksheets("仕訳伝票").Cells(i, j) = ""

        Next

    Next

End Sub