VBAでCOUNTIFを使いこなすための重要事項

今回の問題

C5以降、列に、地名でも食べ物の名前でもいいので4種類、50個ほどランダムに並べてください。
C2に記入した単語と同じ単語をCOUNTIFで数えるとします。

私は、渋谷、品川、池袋、新宿の4つの単語を利用しました。

Excelとは勝手が違うVBAのCOUNTIF

指定した単語がデータの中にいくつあるのか、数値を求めたい時があると思います。
ExcelであればCOUNTIFで一発ですが、このCOUNTIFをVBAで使うとなるとなかなかに厄介でした。
忘備録として、私が VBAでCOUNTIFを正常に機能させるまでの奮闘記をここに残しておきます。

Excelの要領ででとりあえず私が書いてみたCOUNTIFのコード

Sub cnt()
Dim nmb As Long

nmb = WorksheetFunction.CountIf(Range("C5:C65"), C2)
Debug.Print nmb

End Sub

F5で実行してみると、次のようなエラーがでました。

キャプチャでうまくいかなかったのですが、C2の文字がハイライトされています。
ExcelのようにC2の表記では、値として認識してくれない模様。
かといって"C2"とすると、今度はC2という文字列になってしまいますし。

ここは初心に返り、数を求めたい単語をダイレクトに指定してあげましょう。
C2を個数を求めたい"池袋"に書き換えます。

nmb = WorksheetFunction.CountIf(Range("C5:C65"), "池袋")
Debug.Print nmb

お！直接単語を指定してあげると数えてくれるようです。
イミディエイトウィンドウに13の文字が。

注：見間違えでC65をC61としてましたので13ですが、以降はC65に書き直して14とカウントされます。うっかりミスです。すみません。

しかしこれでは、求めたい数値の単語が変わるたびにVBAコードを書き変えなければなりません。
なんとか取得できないものか。。。
セルの値を取得したいので、セル範囲を指定してあげましょう。

nmb = WorksheetFunction.CountIf(Range("C5:C65"), Range("C2"))
Debug.Print nmb

来ました！C2ではなく、ちゃんとセル範囲を指定してあげればよかったのです。

ただ、これではいまいちです。
データがC65よりも増えた時に、またいちいち書き直しますか？

さてさて、どんどんVBAのコードらしくしていきますよ！

とりあえず、最終行を取得する変数iを設定して、範囲をCellsで指定してあげましょう。

Sub cnt()
Dim nmb As Long
Dim i As Long
i = Cells(Rows.count, 1).End(xlUp).Row

nmb = WorksheetFunction.CountIf(Range(Cells(5, 3), Cells(i, 3)), Range("C2"))
Debug.Print nmb

End Sub

お～！サクサク数えてくれますね。

しかし、この関数、シートがアクティブではなくなると、数えてくれません。
セルしか指定してないんですもの。そのため、現在アクティブになっているシート上で動いてしまいます。

空のsheets(2)がアクティブになると、0を返してきます。

ど素人な私は、シートを指定するために、このようなコードを書いてしまいました。
nmb = Sheets(1).WorksheetFunction.CountIf(Range(Cells(5, 3), Cells(i, 3)), Range("C2"))
Debug.Print nmb

写真の通りのエラーが出ました。nmbの行が一気に黄色にハイライトされました。

ググりまくりましたよ。どこでシートを指定すりゃいいの？？

とりあえず、シートの指定、そこじゃないよ。

では、これでどうでしょう？
nmb = WorksheetFunction.CountIf(Sheets(1).Range(Cells(5, 3), Cells(i, 3)), Range("C2"))
Debug.Print nmb

ダメです。まだエラーが出ます。

ならば、表示されているCellsすべてのシートを指定しちゃいましょう！
i = Sheets(1).Cells(Rows.count, 1).End(xlUp).Row

nmb = WorksheetFunction.CountIf(Sheets(1).Range(Sheets(1).Cells(5, 3), Sheets(1).Cells(i, 3)), Sheets(1).Range("C2"))
Debug.Print nmb

ほっ！！数えてくれました。

厄介でしたがVBAでCOUNTIFを使うコツがわかりました

一通りエラーしまくった挙句、ようやくこのブログを執筆しているので、スムーズに数え終わりましたが、まぁ、エラーが出るたびに、何が悪いのか何度もググりまくりましたよ。

要は

1. 条件のセル範囲をきちんと指定してあげること。
2. セルにはすべてシートを指定してあげること。

この二つを満たしてあげれば、うまくCOUNTIFしてくれるようです。
また、ブックをまたいでコードを書いている場合は、シートの前にブックまで指定してあげる必要があります。

Excelで関数をつかう場合と違い、VBAではシートをまたいだり、ブックをまたいだりするので、位置関係を詳細に示してあげる必要があるんですね。
面倒くさいですが、変数や定数をうまく使いこなして、すっきりとしたコードが書ければ幸いです。

あ～、疲れた(笑)