Excel VBAで変動する行数から日数の計算

質問

ExcelのVBAを使い下記のようなことをしたいと考えています。

「問題の発生した日数を月毎に計算」（件数ではなく日数です）

前提条件として以下のようなものが手元にあります。

・問題が発生した日、時間、内容が1行1件で出力されたCSVファイル（月毎にファイルは別れているので、1つのファイルの中に何日あるかの判定だけで良いです）
sample
　2018.10.5,11：05,問題A
　2018.10.5,15：06,問題B
　2018.10.12,16：33,問題B
　2018.10.25,10.55,問題C

これをExcelの関数でやるには
=SUMPRODUCT(1/COUNTIF(A1:A25,A1:A25))
のようにすればいいかと思います。
（ここまで合ってますでしょうか）

これをExcel VBAに入れ込みたいのですが、どうしたらよいかわからない部分があります。
このCSVは発生した件数分行数があり、月により行数が異なります。
その為、まず末尾の行数を先に調べて、変数として関数に入れ込んでやる必要があると考えています。

なので、以下のように考えましたが動作せずどこが間違っているかわからない状態です。
ご教示いただけますと幸いです。
なお、本来はこの手の計算をVBAでやるのであれば関数を埋め込むのではなくVBA上で計算すべきというご意見もあるかとは存じますが、他の部分への流用性なども考慮し、関数に埋め込むことを前提でお願いいたします。

＜作ってみた式＞
MaxRow = Range("A1").End(xlDown).Row
MsgBox Application.WorksheetFunction.SUMPRODUCT(1/Application.WorksheetFunction.COUNTIF(A1:MaxRow,A1:MaxRow))

最近VBAの勉強を始めたばかりの初心者故、基本が出来ていない部分があるとは思います。
式を書く上で上記から抜け落ちている大原則とかもありましたらご指摘いただけますと大変助かります。

よろしくお願いいたします。

回答No.1

watabe007 · ベストアンサー

SUMPRODUCT関数はWorksheetFunctionで使えないようです。
代わりにEvaluateメソッド使って
MaxRow = Range("A1").End(xlDown).Row
MsgBox Evaluate("SUMPRODUCT(1/COUNTIF(A1:A" & MaxRow & ",A1:A" & MaxRow & "))")

お礼

ありがとうございました。
Evaluateというメソッドは知らなかったので勉強になりました。

回答No.3

imogasi

#2です。補足。
日付 　時刻　 問題
2018.10.05　 11:05　 問題A
2018.10.05　 15:06　 問題B
2018.10.12　 16:33　 問題B
2018.10.25　 10.55　 問題C
のようなシートのデータから
ピボットテーブルを使うと、一瞬で
データの個数 / 問題 列ラベル
行ラベル　 問題A　 問題B　 問題C　 総計
2018.10.05　 1　 1　 　 2
2018.10.12　 　　 1　 1
2018.10.25 　 1 1
総計 　1 　　2　 　1 　　4
の表ができます。ピボットテーブルの威力。
ーーー
私が言う、ＶＢＡロジックでは
Sub test06()
Dim f As Object
For i = 2 To 5
MsgBox Cells(i, "A")
Set f = Range("E2:E5").Find(Cells(i, "A"))
If Not (f Is Nothing) Then
MsgBox f.Row
Cells(f.Row, "F") = Cells(f.Row, "F") + 1
Else
End If
Next i
End Sub
のようなコードの実行で、下記Ｆ列が出ます。
Ｅ列は、フィルタの詳細設定で、Ａ列データに対し、「重複するデータは無視する」で出した、Ａ列データに現れる、日付リストです。日付は文字列のままでやってます。
日付 時刻 問題 日付 日付頻度　（　Ｅ列　　Ｆ列）
2018.10.05 11:05 問題A 2018.10.05 2
2018.10.05 15:06 問題B 2018.10.12 1
2018.10.12 16:33 問題B 2018.10.25 1
2018.10.25 10.55 問題C

回答No.2

imogasi

VBAでやるというよりも、ピボットテーブルなどを使うほうが良いのでは。
またマクロの記録で、それ（ピボットテーブル利用）をＶＢＡコード化できる。
将来のため、勉強のため、ロジックでやるなら、
重複のない日付のリストを作って、その日付に該当する件数を、ロジックやワークシート関数を使って、簡単に出せる。
ロジックでやるのは、将来の力をつけるためで、仕事忙しい中でこういう方法をあれこれ考えるのは、上司からすると、望ましくない。
ここでロジックと言っているのは、データ１行ずつ読んで、件数を足しこむための該当日付を見つけて、＋１していくやり方を言っている。
ーー
エクセルで扱うのだから、まずＣＳＶファイルをシートに読み込むコードをＷＥＢで探すべき。データーデータツールー区切り位置の機能も使えるだろう。
日付部分（列）は、日付シリアル値にするか、文字列のまま扱うなら、例えば１日は０１のようなデータにするほうが扱いやすかろう。