[VBA実践]Excel関数を有効活用しよう![#4]
記事IT・テクノロジー
前回の記事はこちら
さて、前回の記事で、CellsとRangeを組み合わせ、スマートなコードを書くことが出来ました。
それが以下になります。
Sub 担当者ごと抽出()
Dim targetName As String
Dim rowLong As Long
Dim exRowLong As Long
targetName = Cells(1, 9).Value
exRowLong = 2
For rowLong = 2 To 16
If Cells(rowLong, 6).Value = targetName Then
'一緒だった時の処理
Debug.Print "担当者名が" & targetName & "と同じです!"
Range(Cells(exRowLong, 11), Cells(exRowLong, 16)).Value = Range(Cells(rowLong, 1), Cells(rowLong, 6)).Value
exRowLong = exRowLong + 1
Else
'そうでない時の処理
Debug.Print "担当者名が" & targetName & "ではありません!"
End If
Next
End Sub
このコードの問題点は、For文の範囲が2~16となっており、結果として「16行目までしか対応出来ない」という点です。
17行目以降に値が入る場合、コードを書き換える必要が出てしまいます。
この部分を、今回の記事で解決したいと思います。
シートの値を活用する
前提としてですが、マクロを作る際には、出来るだけコードを書き換える必要が無いように作りましょう。
自分だけが使う場合でも、データの数などによっていちいちコードを書き換えていると面倒です。
今のように数十行規模のコードの一部を変えるのも、何度もやっているとめんどくさくなりますし、書き換える必要が無い部分を書き換えてしまえばバグを生み出し、それの解消に時間を費やすことになったりします。
そうなると、楽をするためにマクロを作ったのに、逆に時間が掛かる、なんて事になりかねません。
そうならないためにも、使う時に「この値は頻繁に変える必要が出てくるなぁ」という値については、VBAのコード上で値を設定するのではなく、シート上に入力した値を活用するようにしましょう。
例えば今回であれば、
For rowLong = 2 To 16
この部分の、2と16が「VBAのコード上で設定している値」になります。
開始行数を示す2はまだしも、最終行数についてはデータが増えれば増えるほど大きくなるので、16としてしまっているのはまずいですよね。
というわけで、シート上に、以下の画像のような設定欄を作ってみましょう。
I6セルに、開始行。I7セルに、最終行となる値を入力出来るようにします。
(開始行はいちいち設定出来るようにしなくても良い気がしますが一応)
このようにしておき、この値をFor文の値の範囲指定に使えば、わざわざVBAのコードを書き直さなくても、シート上の操作だけで対応出来るようになります。
ついでに設定欄をシート上に作っておくと、マクロを使う人にとっても、どこからどこまでを対象として処理が行われるのかが分かりやすいですよね。
では変更をコードに反映していきます。
開始行と最終行の値はFor文の範囲指定でしか使いませんが、一応変数にしておきましょうか。
セルに入力した値を変数に代入する方法は、もう大丈夫ですよね?
Sub 担当者ごと抽出()
Dim targetName As String
Dim rowLong As Long
Dim exRowLong As Long
Dim startRow As Long
Dim endRow As Long
targetName = Cells(1, 9).Value
exRowLong = 2
startRow = Cells(6, 9).Value
endRow = Cells(7, 9).Value
For rowLong = startRow To endRow
If Cells(rowLong, 6).Value = targetName Then
'一緒だった時の処理
Debug.Print "担当者名が" & targetName & "と同じです!"
Range(Cells(exRowLong, 11), Cells(exRowLong, 16)).Value = Range(Cells(rowLong, 1), Cells(rowLong, 6)).Value
exRowLong = exRowLong + 1
Else
'そうでない時の処理
Debug.Print "担当者名が" & targetName & "ではありません!"
End If
Next
End Sub
開始行をstartRow、最終行をendRowとして、それぞれCellsを使ってセルの値を代入します。
そしてその変数を
For rowLong = 2 To 16
だった部分に当てはめて、
For rowLong = startRow To endRow
とするだけですね。
これでシート状からループする範囲を設定出来るようになりました!
マクロでもExcel関数を活用する
さて、ここまで見た人には、正直こう思っていただきたいです。
「いや、最終行の値わざわざ手入力するのめんどくさいよ」
…もちろんコードをいちいち書き換えることに比べれば、かなり楽です。
ただ、最終行数をいちいち確認してセルに入力するとかちょっと微妙です。
となると「VBAでデータの最終行を取得しよう!」という話になりがちですが、ちょっと小難しい方法が多いので、あんまり一般的ではない方法をお教えしつつ、新しい発想を覚えていただきます。
それは、「VBAとExcel関数を組み合わせる」ということです。
僕のVBA解説・実践シリーズで一貫してお伝えしたいことの一つが、
「目的とする処理を実現するのが最優先」
ということです。
目的を達成出来るなら、VBAだけを使った方法にこだわる必要はありません。
正直邪道だと思いますが、Excel関数を使えるのは「Excel」マクロの最大のメリットです。
というわけで、Excel関数で最終行数を取得する方法を考えましょう。
ちなみに今のシート構成はこんな感じですね。
A列がデータのナンバーを示す列になり、「必ず入っているはず」なので、
「A列に値がある=処理対象のデータ」ということになりますね。
となると、「A列に何かしら入っているセルの数」、もしくは「A列の値の最大値」あたりが「最終行数」のヒントになりそうじゃないですか?
セルに何か値が入っている場合にそれを数えてくれる関数がCOUNTA関数です。
COUNT関数だと数値以外数えてくれないので、今回はCOUNTA関数を使います。
A列すべての中でいくつ値が入っているセルがあるかを数えるので、
=COUNTA(A:A)
という数式を、最終行を入力していたセルに入れてみます。
最終行はたしかに16なので、完璧ですね!
次に、A列に入力されている最大の値を取得する方法もやってみます。
最大値を取得するにはMAX関数を使います。
=MAX(A:A)
とすると、A列の最大値を取得出来ます。
確かにNoの最大値を取得出来ましたが、「最終行数」の16とは1違いますね。
当たり前ですが、Noの値は行数より1小さいので、その分調整してあげる必要があります。
ということで、
=MAX(A:A)+1
としましょう。
これで大丈夫ですね!
ちなみにCells().ValueではExcel関数の結果も取得出来るので、VBAのコードは特に変更する必要がありません。
まとめと次回予告
今回の記事は「VBA実践」と言いながら、Excel関数の解説がメインになりました。
ただ、繰り返しになりますが、Excelマクロである以上、非常に便利なExcel関数を利用しない手はありません。
VBAで最終行を取得する方法も調べればすぐに出てきますが、その有名な方法はVBAにもう少し慣れてからでも大丈夫です。
目的となる処理を実現するために、自分が使える手札を最大限活用することを意識すれば、VBAだけで対応するよりも楽な場面は必ず出てきます。
「こういうことはExcel関数の方が楽だな」
「これはVBAじゃないと駄目そうだな」
という役割分担を自分の中で考えてみると良いですね。
さて、次の記事では、
「複数のワークシートを操作するマクロの書き方」を解説します。
これまでは一つのシート内で完結している内容でしたが、実際には複数のシートを使うことが多々あります。
そんな時の書き方を解説しつつ実践していただきます。
今回の内容で疑問点がある方は、以下の有料個別サポートをご利用下さい。
出来るだけ迅速に、丁寧に対応致します。
今回も最後までお疲れさまでした!
