前回一番下のセルまで移動させるマクロを作りましたが、次は
日付が前後した場合、ワンクリックで日付順に並べ替えるマクロを紹介します。
今までセルの指定の仕方は、Cells(1,5)といった指定の仕方でした。
この指定の仕方だと、一つのセルしか指定できません。
ある範囲のセルを指定したい場合、次のように記述します。
前回一番下のセルまで移動させるマクロを作りましたが、次は
日付が前後した場合、ワンクリックで日付順に並べ替えるマクロを紹介します。
今までセルの指定の仕方は、Cells(1,5)といった指定の仕方でした。
この指定の仕方だと、一つのセルしか指定できません。
ある範囲のセルを指定したい場合、次のように記述します。
行数が100、200となると次入力するところまで行を移動させる
必要が出ていきます。
かといって、シートを変えるのは後で会計ソフトにインポートさせる
ことを考えるとあまりお勧めできません。
今回は、ワンクリックで入力行まで移動するマクロを紹介します。
日付の欄に空白がある場合の処理の追加の解説です。
B列の動きについて解説をします。
コードです。
If Cells(a, 2).Value = “” Then
※Cells(a, 2).Value = Cells(a – 1, 2).Value
End If
これもaに数字を当てはめていけば難しくはありません。
シート間のデータのやりとりについて追加したプログラムです。
Dim a As Integer
a = 3
Worksheets(“元のデータ”).Activate
Do Until Cells(a, 1).Value = “”
Worksheets(“インポート”).Cells(a – 1, 1).Value = Cells(a, 1).Value
Worksheets(“インポート”).Cells(a – 1, 5).Value = Cells(a, 2).Value
If Cells(a, 4).Value = “” Then
Worksheets(“インポート”).Cells(a – 1, 2).Value = 101
Worksheets(“インポート”).Cells(a – 1, 3).Value = 999
Worksheets(“インポート”).Cells(a – 1, 4).Value = Cells(a, 3).Value
Else
Worksheets(“インポート”).Cells(a – 1, 3).Value = 101
Worksheets(“インポート”).Cells(a – 1, 2).Value = 999
Worksheets(“インポート”).Cells(a – 1, 4).Value = Cells(a, 4).Value
End If
If Cells(a, 2).Value Like “*印紙*” Then
Worksheets(“インポート”).Cells(a – 1, 2).Value = 726
End If
If Cells(a, 2).Value Like “*切手*” Then
Worksheets(“インポート”).Cells(a – 1, 2).Value = 724
End If
If Cells(a, 2).Value Like “*引出し*” Then
Worksheets(“インポート”).Cells(a – 1, 3).Value = 121
End If
a = a + 1
Loop
End Sub
①シート名「インポート」を参照しているときは、Cells(a-1,1)
②シート名「元のデータ」を参照しているときは、Cells(a,1) となっています。
これはシート名「インポート」では、3行目からデータがスタートしていますが、
シート名「元のデータ」では2行目からデータがスタートしています。
このずれを調整するためにシート名「インポート」ではa-1としています。
Worksheets(“インポート”).Cells(a – 1, 1).Value = Cells(a, 1).Value
a=3のとき
Worksheets(“インポート”).Cells(a – 1, 1).Value →A2
Cells(a, 1).Value →A3
・・・
a=12のとき
Worksheets(“インポート”).Cells(a – 1, 1).Value →A11
Cells(a, 1).Value →A12
このようにシートによってどの行からスタートさせるのか違う場合は変数に対して
引き算で調整します。
今までは同じシート間でのデータのやり取りでしたが、同じシート同士よりも
出納帳のデータとインポートするデータは、別のシートの方が管理しやすいです。
続きを読む
これが完成型のプログラムです。
Sub 変換()
Dim a As Integer
a = 3
Do Until Cells(a, 1).Value = “”
Cells(a, 7).Value = Cells(a, 1).Value
Cells(a, 11).Value = Cells(a, 2).Value
If Cells(a, 4).Value = “” Then
Cells(a, 8).Value = 101
Cells(a, 9).Value = 999
Cells(a, 10).Value = Cells(a, 3).Value
Else
Cells(a, 9).Value = 101
Cells(a, 8).Value = 999
Cells(a, 10).Value = Cells(a, 4).Value
End If
If Cells(a, 2).Value Like “*印紙*” Then
Cells(a, 8).Value = 726
End If
If Cells(a, 2).Value Like “*切手*” Then
Cells(a, 8).Value = 724
End If
If Cells(a, 2).Value Like “*引出し*” Then
Cells(a, 9).Value = 121
End If
a = a + 1
Loop
End Sub
これが基本形です。
最初のうちは、セルがどこを参照しているのか、理解しにくかも
しれません。そんなときはF8を押して一行ずつコードを進め、変数が
今いくつでどこのセルを参照しているのかしっかり確認する、または
自分でCells(a,○)がどこのセルなのかしっかり確認して下さい。
またa=a+1を忘れると無限ループに突入するので気を付けて下さい。
最後に大事なこと、コートが書き終わったら必ず保存してから実行して
下さい。マクロを実行しても意図したとおりにならなかった場合、
元に戻そうと思っても戻せません。そんなときは、実行前に保存して
元の状態に戻すときは保存しないで終了し、再度そのファイルを開いて
下さい。
科目の判断もExcelVBAでできます。
科目の判断までパソコンにやらせることに、不安を感じる方もいらっしゃるかも
しれませんが、ご安心下さい。
例えば、
①摘要の中に「印紙」という言葉があれば、租税公課
②摘要の中に「切手」という言葉があれば、通信費
③摘要の中に「引出し」という言葉があれば、普通預金
等々
結局人もパソコンも考え方は同じです。もし間違いがあればあとでソフトの方で
直せばそれで済む話です。
ではプログラムはどう書くか?
①の場合
If Cells(a, 2).Value Like “*印紙*” Then
Cells(a, 8).Value = 726 →(租税公課の科目CD)
End If
②の場合
If Cells(a, 2).Value Like “*切手*” Then
Cells(a, 8).Value = 724 →(通信費の科目CD)
End If
③の場合
If Cells(a, 2).Value Like “*引出し*” Then
Cells(a,9).Value = 121 →(普通預金の科目CD)
End If
こんな具合です。
「Like “*○×*”」は、○×というキーワードを含むという意味です。
このコードをどこに当てはめるのかというと、
Sub 変換()
・・・
End If
→ ここです!
a = a + 1
Loop
End Sub
実行後の画面です。もうあとは会計ソフトにそのままインポートするだけです。
このやり方だと、科目を判別したい摘要の数だけIF関数のコードを追加して頂く必要
があります。ですのですべての摘要を入れることは難しいですが、よく使うものだけを
コードに追加して下さい。
それでも振込料、飲食代、交際費、交通費、消耗品等々20くらい追加すれば、7割8割は
フォローできるのではないかと思います。
入金取引と出金取引の場合の解説をします。
入金取引と出金取引の違いは、
現金科目が、入金は借方、出金は貸方
相手科目が、入金は貸方、出金は借方
金額の参照先
の3点です。
当たり前と言えば当たり前ですが、これをプログラムで書くので最初に
整理しておきます。
これをVBAで書くと、
入金の場合
Cells(a, 8).Value = 101 →借方科目に現金の科目コード
Cells(a, 9).Value = 999 →貸方科目に諸口の科目コード
Cells(a, 10).Value = Cells(a, 3).Value →金額の参照先
出金の場合
Cells(a, 9).Value = 101 →貸方科目に現金の科目コード
Cells(a, 8).Value = 999 →借方科目に諸口の科目コード
Cells(a, 10).Value = Cells(a, 4).Value
これ以外の日付と摘要は入金取引も出金取引も同じなので、
Cells(a, 7).Value = Cells(a, 1).Value
Cells(a, 11).Value = Cells(a, 2).Value
と、この2行はIF関数とは関係のないところに記述します。
ちなみに、A列はCells(a,1),B列はCells(a,2),C列はCells(a,3),D列はCells(a,4),E列はCells(a,5)
F列はCells(a,6),G列はCells(a,7),H列はCells(a,8),I列はCells(a,9),J列はCells(a,10)です。
くどいようですが、A列から数えてもらえばわかると思います。
これで一通りの解説が終わりました。