[[20130209180757]]　『VBAでSUMPRODUCTを使うとエラーが出る』（しんぽん）　ページの最後に飛ぶ

『VBAでSUMPRODUCTを使うとエラーが出る』（しんぽん）
　データ入力シートからマクロを使って集計シートのセルに数値を入力
させたいのですが、SUMPRODUCTがうまくいかずmysh.Cells(j,i)全てのセルに
エラー#NAME?が出ます。コードは以下のようになっています。配列変数MaxRow()
,clmcnt(),rwcnt()はすべて正しい値が入っています。どこが間違いなのか
教えていただけないでしょうか。宜しくお願い致します。

Sub shukeikyuryou()

Dim myfile As String
Dim mysh As Worksheet
Dim mysh1 As Worksheet
Dim mycnt As Long
Dim MaxRow() As Long
Dim clmcnt() As String
Dim rwcnt() As Long
Dim i As Long
Dim j As Long
Dim k As Long

'集計ファイルのファイル名をつける

    myfile = ActiveWorkbook.Name

'シート名をつける

    Set mysh = Worksheets("集計表")
    Set mysh1 = Worksheets("給料入力分")

'給料入力分のデータ数把握

    Workbooks(myfile).Worksheets("給料入力分").Activate

    For k = 1 To 5
        ReDim MaxRow(1 To k)
        MaxRow(k) = mysh1.Cells(3, 3 * (k - 1) + 2).End(xlDown).Row - 2
    Next k

'＊集計ファイルの給料入力分データ

    Workbooks(myfile).Worksheets("集計表").Activate
    On Error Resume Next

    For i = 4 To 13
    Select Case i
    Case 6, 11, 12
    Case Else

            ReDim clmcnt(4 To i)
            clmcnt(i) = mysh.Cells(3, i).Value

        For j = 4 To 104
        Select Case j
        Case 30, 36, 59, 74
        Case Else

                ReDim rwcnt(4 To j)
                rwcnt(j) = mysh.Cells(j, 2).Value

　　　　'ここがうまくいきません！５つの式の合計を出したいのですが、とりあえず
　　　　　ひとつめの式でうまく動作をするかどうか検証中です。

                mysh.Cells(j, i).Value = _
                Evaluate("SumProduct((mysh1.Range(Cells(3, 1), Cells(2 + MaxRow(1), 1)) = rwcnt(j)) * (mysh1.Range(Cells(3, 3), Cells(2 + MaxRow(1), 3)) = """ & clmcnt(i) & """) * mysh1.Range(Cells(3, 2), Cells(2 + MaxRow(1), 2)))")  '_

              '+ Evaluate("SumProduct((mysh1.Range(Cells(3, 4), Cells(2 + MaxRow(2), 4)) = rwcnt(j)) * (mysh1.Range(Cells(3, 6), Cells(2 + MaxRow(2), 6)) = clmcnt(i)) * (mysh1.Range(Cells(3, 5), Cells(2 + MaxRow(2), 5))))") _

              '+ Evaluate("SumProduct((mysh1.Range(Cells(3, 7), Cells(2 + MaxRow(3), 7)) = rwcnt(j)) * (mysh1.Range(Cells(3, 9), Cells(2 + MaxRow(3), 9)) = clmcnt(i)) * (mysh1.Range(Cells(3, 8), Cells(2 + MaxRow(3), 8))))") _

              '+ Evaluate("SumProduct((mysh1.Range(Cells(3, 10), Cells(2 + MaxRow(4), 10)) = rwcnt(j)) * (mysh1.Range(Cells(3, 12), Cells(2 + MaxRow(4), 12)) = clmcnt(i)) * (mysh1.Range(Cells(3, 11), Cells(2 + MaxRow(4), 11))))") _

              '+ Evaluate("SumProduct((mysh1.Range(Cells(3, 13), Cells(2 + MaxRow(5), 13)) = rwcnt(j)) * (mysh1.Range(Cells(3, 15), Cells(2 + MaxRow(5), 15)) = clmcnt(i)) * (mysh1.Range(Cells(3, 14), Cells(2 + MaxRow(5), 14))))")

          End Select
        Next j
        End Select
    Next i
End Sub

EXCEL2000、WinXPです。

 マクロを走らせたわけではありませんが、気がついた点。
mysh1.Range(Cells(3, 1), Cells(2 + MaxRow(1), 1))
この時Cells(3, 1)は正確にはmysh1.Cells(3, 1)ですが、
mysh1を省略しているために「その時点でアクティブなシート」が
取得されます。他のCellsも同様です。
Rangeプロパティで取得しようとしているRangeオブジェクトと
Cellsプロパティで取得しようとしているRangeオブジェクトの
親となるシートが異なるのでこのぶぶんはエラーになるはずです。
　
なおかつ、on error でのエラー回避でどこでエラー発生したのか
わからなくしています。
単にエラーをスルーするためのエラートラップは感心しません。
（みやほりん）

 +
 Evaluate method で数式の結果を求める場合、全て文字列にする必要あり。

 mysh1.Range(Cells(3, 1), Cells(2 + MaxRow(1), 1)
 これ文字列じゃないよね？　他にも沢山あるけど。
(seiya)

みやほりん様、seiya様

　ご教授ありがとうございました。ご指摘いただいた内容を参考に、まずはRange(Cells(),Cells())の
Cellsの前にシート名を記入し、Evaluatemethodを避けてApplication.Worksheetfunction.SumProductで
やってみたのですがうまくいきません。次にEvaluatemethodを使って全てを以下のように文字列に書き
換えたのですが、コンパイルでエラーが出ます。きっと文字列の書き換え方が間違っていると思うの
ですがどこが間違っているのか分かりません。再度、ご教授いただけないでしょうか？

 mysh.Cells(j, i).Value = _
                Evaluate("SumProduct((mysh1.Range("A3:A" & 2 + MaxRow(1)) = "" & rwcnt(j) & "") * (mysh1.Range("C3:C" & 2 + MaxRow(1)) = "" & clmcnt(i) & "") * (mysh1.Range("B2:B" & 2 + MaxRow(1)))")

 mysh.Cells(j, i).Value = _
     .Parent.Evaluate("SumProduct((" & mysh1.Range("A3:A" & 2 + MaxRow(1)).address & _
     "=""" & rwcnt(j) & """)*(" & mysh1.Range("C3:C" & 2 + MaxRow(1)).address _
     & "=""" & clmcnt(i) & """)*(" & mysh1.Range("B2:B" & 2 + MaxRow(1)).address & ")")

 こんな感じ？(未検証)

 くどいようだけど、mysh1.Range("A3:A" & 2 + MaxRow(1)) は文字列じゃないよ？
 セルに数式を入れる要領なんだけど...
 (seiya)

seiya様

　ご教授ありがとうございます。早速上記コードを使用してみたところ、.Parentのところで
コンパイルエラーが出ます。これはどのように対応したら良いのでしょうか？

 .Parent.Evaluate を mysh.Evaluate に変更してみてください。
 (seiya)

 全体を見ていませんが、

 >配列変数MaxRow(),clmcnt(),rwcnt()はすべて正しい値が入っています。
 とありますが、Redim でせっかく計算した値が消えていませんか？

 前の値を保持するには Preserve が必要です。
 (Mook)

seiya様、Mook様

　ご指導ありがとうございます。配列変数は全てRedim Preserveで定義し直しました。
.Parent.Evaluateはmysh.Evaluateに変更しました。結果コンパイルのエラーは無くなり
ましたが、全てのmysh.Cells(j,i)は#VALUE!になりました。度々申し訳ないのですが、
何か考えられる要因がありましたら教えていただけないでしょうか？

みやほりん様、seiya様、Mook様

 　Rangeを使うとどうしてもうまく処理できないので、前にこのコードを入れて
 mysh1n = mysh1.Name

　以下のようなコードでうまく動くようになりました。
mysh.Cells(j, i).Value = _

 Evaluate("SumProduct((" & mysh1n & "!A3:A" & MaxRow(1) + 2 & "=" & rwcnt(j) & ")* _
 (" & mysh1n & "!C3:C" & MaxRow(1) + 2 & "=""" & clmcnt(i) & """)* _
 (" & mysh1n & "!B3:B" & MaxRow(1) + 2 & "))")

　このたびは色々ご教授いただき誠にありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20130209180757]] 『VBAでSUMPRODUCTを使うとエラーが出る』（しんぽん） ページの最後に飛ぶ

[[20130209180757]]　『VBAでSUMPRODUCTを使うとエラーが出る』（しんぽん）　ページの最後に飛ぶ