Excel VBA の質問

Excel VBA初心者です。
以下のVBAを簡略化したいのですがどうすればいいでしょうか？


Sub Macro1()
Dim cnt As Long, cnt2 As Long, cnt3 As Long, cnt4 As Long, cnt5 As Long, cnt6 As Long, cnt7 As Long, cnt8 As Long, cnt9 As Long, cnt10 As Long, cnt11 As Long, cnt12 As Long, cnt13 As Long, cnt14 As Long, cnt15 As Long, cnt16 As Long, cnt17 As Long, cnt18 As Long, cnt19 As Long, cnt20 As Long, cnt21 As Long
cnt = Evaluate("SUMPRODUCT(((F:F)=I9)*((G:G)=H10))")
cnt2 = Evaluate("SUMPRODUCT(((F:F)=J9)*((G:G)=H10))")
cnt3 = Evaluate("SUMPRODUCT(((F:F)=K9)*((G:G)=H10))")
cnt4 = Evaluate("SUMPRODUCT(((F:F)=L9)*((G:G)=H10))")
cnt5 = Evaluate("SUMPRODUCT(((F:F)=M9)*((G:G)=H10))")
cnt6 = Evaluate("SUMPRODUCT(((F:F)=N9)*((G:G)=H10))")
cnt7 = Evaluate("SUMPRODUCT(((F:F)=O9)*((G:G)=H10))")
cnt8 = Evaluate("SUMPRODUCT(((F:F)=P9)*((G:G)=H10))")
cnt9 = Evaluate("SUMPRODUCT(((F:F)=Q9)*((G:G)=H10))")
cnt10 = Evaluate("SUMPRODUCT(((F:F)=R9)*((G:G)=H10))")
cnt11 = Evaluate("SUMPRODUCT(((F:F)=S9)*((G:G)=H10))")
cnt12 = Evaluate("SUMPRODUCT(((F:F)=T9)*((G:G)=H10))")
cnt13 = Evaluate("SUMPRODUCT(((F:F)=U9)*((G:G)=H10))")
cnt14 = Evaluate("SUMPRODUCT(((F:F)=V9)*((G:G)=H10))")
cnt15 = Evaluate("SUMPRODUCT(((F:F)=W9)*((G:G)=H10))")
cnt16 = Evaluate("SUMPRODUCT(((F:F)=X9)*((G:G)=H10))")
cnt17 = Evaluate("SUMPRODUCT(((F:F)=Y9)*((G:G)=H10))")
cnt18 = Evaluate("SUMPRODUCT(((F:F)=Z9)*((G:G)=H10))")
cnt19 = Evaluate("SUMPRODUCT(((F:F)=AA9)*((G:G)=H10))")
cnt20 = Evaluate("SUMPRODUCT(((F:F)=AB9)*((G:G)=H10))")
cnt21 = Evaluate("SUMPRODUCT(((F:F)=AC9)*((G:G)=H10))")
Range("I10").Value = cnt
Range("J10").Value = cnt2
Range("K10").Value = cnt3
Range("L10").Value = cnt4
Range("M10").Value = cnt5
Range("N10").Value = cnt6
Range("O10").Value = cnt7
Range("P10").Value = cnt8
Range("Q10").Value = cnt9
Range("R10").Value = cnt10
Range("S10").Value = cnt11
Range("T10").Value = cnt12
Range("U10").Value = cnt13
Range("V10").Value = cnt14
Range("W10").Value = cnt15
Range("X10").Value = cnt16
Range("Y10").Value = cnt17
Range("Z10").Value = cnt18
Range("AA10").Value = cnt19
Range("AB10").Value = cnt20
Range("AC10").Value = cnt21

End Sub

かなりの初心者ですのであまり難しくないように回答頂ければ助かります。
現状で動かすとかなり処理が遅いので困ってます。

No.1 ベストアンサー 回答者： tom04 回答日時： 2015/03/29 01:18

こんばんは！

SUMPRODUCT関数は配列数式ですので、列全体を参照範囲としてしまうと
PCにとってはかなりの負担になります。

お使いのバージョンが不明なのですが、Excel2007以降のバージョンですと
COUNTIFS関数が使用できます。
すなわちI10セルに
=COUNTIFS($F:$F,I9,$G:$G,$H10)
という数式を入れフィルハンドルでAC10セルまでコピー！
これで大丈夫だと思います。

どうしてもVBAでやりたい場合は

Sub Sample1()
With Range("I10:AC10")
.Formula = "=COUNTIFS($F:$F,I9,$G:$G,$H10)"
.Value = .Value
End With
End Sub

くらいで大丈夫だと思います。

いかにもVBAらしくやれば

Sub Sample2()
Dim j As Long
For j = 9 To 29 '//Ｉ列～AC列まで
Cells(10, j) = WorksheetFunction.CountIfs(Range("F:F"), Cells(9, j), Range("G:G"), Range("H10"))
Next j
End Sub

といった感じでですかね。m(_ _)m

この回答へのお礼

ありがとうございました。
最終的には以下の内容でできました。
すごいですねCuntIfsって、この関数は知りませんでした。
実はWorksheetsFunction.SUMPRODUCT　で頑張ってつくってましたが
うまくいかなかったんです。すごく助かりました。

Sub dekiagari1()
Dim i As Long, n As Long
For i = 9 To 29 '//Ｉ列～AC列まで
For n = 10 To 51 '//H10～H45行まで
Cells(n, i) = WorksheetFunction.CountIfs(Range("F:F"), Cells(9, i), Range("G:G"), Cells(n, 8))
Next n
Next i
End Sub

お礼日時：2015/03/29 08:45