ＶＢＡ countif について

いつもお世話になっております
おしえてくれませんでしょうか
シート1　と　シート2　があります。
同じくＢ列に
A001
A002
A003
A004
A005
A006
A003
A008
とならんでいます。　もっとありますが。
例えば　A003　が　シート1　と　シート2で
何個あるか調べたいのですが、
以下のコードを作りましたが、
Activesheetのみしか　カウントされませんです。
わかる方おしえてくれませんでしょうか

Sub fage()
Dim aya
aya = Array("Sheet2", "Sheet3")
Dim r As Range
Dim wf As Object
Set wf = WorksheetFunction

For Each w In aya
With Worksheets(w)
Set r = .Range("B2", .Cells(Rows.Count, "B").End(xlUp))
cnt = wf.CountIf(r, "A003")

End With

Next

Cells(2, 8) = cnt


Set wf = Nothing
Set r = Nothing

End Sub

質問者からの補足コメント

• 

  もしかして redim preserve とかつかうのでしょうか

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/12/16 20:39

• 

  これだと　変だし　おしえてくれませんでしょうか
  お願い致します。
  Dim x
  cnt = 0
  For Each w In aya
  With Worksheets(w)
  Set r = .Range("B2", .Cells(Rows.Count, "B").End(xlUp))
  ReDim Preserve x(cnt)
  
  x(cnt) = wf.CountIf(r, "A003")
  
  End With
  Next
  Cells(2, 8) = x

    補足日時：2021/12/16 20:44

No.3 ベストアンサー 回答者： mygoonickname 回答日時： 2021/12/16 21:41

こんばんは。

＞例えば　A003　が　シート1　と　シート2で
合計で良いのでしょうか？　それなら、下記の様な感じでどうでしょうか？

Sub fage()
Dim aya
aya = Array("Sheet2", "Sheet3")
Dim r As Range
Dim wf As Object
Set wf = WorksheetFunction

cnt = 0
For Each w In aya
With Worksheets(w)
Set r = .Range("B2", .Cells(Rows.Count, "B").End(xlUp))
cnt = cnt + wf.CountIf(r, "A003")

End With

Next

Cells(2, 8).Value = cnt

Set wf = Nothing
Set r = Nothing

End Sub

この回答へのお礼

ありがとうございました。
cnt = cnt + wf.CountIf(r, "A003")
これなんですね
本当助かります

お礼日時：2021/12/16 21:55

No.2 回答者： fujillin 回答日時： 2021/12/16 21:37

No1です

＞これだと　変だし　おしえてくれませんでしょうか
なさりたいことが「合計を求めること」なのか「個々に個数を求めること」なのか不明なのままなので、とりあえず前者（＝合計）での例。

＞例えば　A003　が　シート1　と　シート2で～～
と文章にはあるけれど、コードは「"Sheet2", "Sheet3"」となっているので、これに関しても不明ですが、コードの内容を採用。

Sub sample()
Dim cnt, w, r
cnt = 0
For Each w In Worksheets(Array("Sheet2", "Sheet3"))
Set r = Range(w.Cells(2, 2), w.Cells(Rows.Count, 2).End(xlUp))
cnt = cnt + WorksheetFunction.CountIf(r, "A003")
Next w

Cells(2, 8).Value = cnt
End Sub

No.1 回答者： fujillin 回答日時： 2021/12/16 20:32

こんばんは

＞Activesheetのみしか　カウントされませんです。
というよりも、「配列の後の方のシート」しかカウントされないのでは？
ループの中で、変数cntに個々のシートの個数を上書きしているだけですので。

合計を求めたいという事でしょうか？
であるなら、（初期値を0にしておいて）ループ内では加算してゆく必要があります。

それぞれの個数を求めたいのなら、
ループ外でシートに書き出すのではなく、ループ内で異なるセルに値を書き出すようにする。

とかでしょうか。