[[20210523102830]]　『串刺し集計のvbaを習いましたが実行エラー1004が潤x（昭久）　ページの最後に飛ぶ

『串刺し集計のvbaを習いましたが実行エラー1004が出てくるのです』（昭久）

Sub test()
Dim mySum As String, r As Range

  mySum = Worksheets(3).Name & ":" & _
          Worksheets(Worksheets.Count).Name & "!"
  Worksheets(1).Activate
  For Each r In Selection
    r.Value = "=sum(" & mySum & r.Address(0, 0) & ")"
    'r.Value = r.Value
  Next r
End Sub　
r.Value がempTYとなりアプリケーションの定義とオブジェクトの定義のエラ-がでます。なにをどう書き換えなければならないか教えて頂けないでしょうか？お願いいたします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 テストランでは特に問題は出なかったですけど、質問と違うコードでやってないですか？

（半平太） 2021/05/23(日) 11:20

Selectionの選択範囲はどのシートも式が入り保護がしてあります
（昭久） 2021/05/23(日) 12:10

 後から何を言い出すかなぁ。。

 私は降ります。

（半平太） 2021/05/23(日) 13:13

＞Selectionの選択範囲はどのシートも
＞式が入り保護がしてあります
いや、Selectionしてるのは、Worksheets(1)のセルだけでしょう。
そして、Worksheets(1)のセルを書き換えようとするなら、そのシートの保護を一時的に解除するか、あらかじめ（ブックを開いたときにでも）UserInterfaceOnlyをTrueにして保護しておけばよいのでは？
（r.Value がEmptyとなり～の意味がよくわからないですが・・・）

（もこな２） 2021/05/23(日) 13:49

半平太さん申し訳ありませんでした。もこな2さんありがとうございました。Worksheets(1)の保護を一時的に解除したりブックを開いたときにUserInterfaceOnly：＝Truer　してもｒ.Value がまだEmptyになりますが、実行エラー1004が出ます。ワークシートのインデックス番号の間違いでしょうか?
（昭久） 2021/05/23(日) 16:13

上記マクロ動きますから
新規ブックに貼って、
目的の仕様に合わせ、設定1つ変更ごとに実行して
どの設定変更でエラーになるか探る。
とか？

集計範囲に　Empty　があるとか？
（ふむ～） 2021/05/23(日) 16:37

 これを実行して、結果を知らせてください。

 Sub test()
     Dim mySum As String, r As Range
     mySum = Worksheets(3).Name & ":" & _
             Worksheets(Worksheets.Count).Name & "!"
     Worksheets(1).Unprotect
     Worksheets(1).Activate
     For Each r In Selection
         r.Value = "=sum(" & mySum & r.Address(0, 0) & ")"
     Next r
     Worksheets(1).Protect
 End Sub

（γ） 2021/05/23(日) 18:10

すいませんありがとうございました。でもまだ実行エラー1004が出てきます
（昭久） 2021/05/23(日) 20:01

ステップ実行（F8）をしてみて、
どのセルでエラーになりますか？
そのときの式はわかりますから、
手でその式をセルに入力してみてください。

それくらいのことはもう実行しているかと思いますが、
まとめて知らせてください。

（γ） 2021/05/23(日) 20:24

私の提示したものは、「そのままコピーペイストして」動作させていますか？
同じようなものだよね、ということで、実際に動かしているものは
別のコードじゃないかなあ、という気がしてきました。

実行させてエラーになったとき、VBE（コードエディタ）を見ると、
どこかの行が黄色くなっているはず。
イミディエイトウインドウに、
?r.Address
とすると、エラーになったセルがどこかわかります。
そこのセルがなぜ計算式を受け付けないのか、エラーになるのか調べて下さい。
その合計の対象となっているセルに、特別なことがないか、
手作業でやったらどうなるか、等々、調べて下さい。

それほど難しい話でもないはずです。

（γ） 2021/05/23(日) 23:16

ちょっと確認。
１、Selectionには、どのようなセル（範囲）が入る予定なんですか？
２、その範囲に結合セルはありますか？

（もこな２） 2021/05/24(月) 09:04

＞r.Value がempTYとなり
ようやく意味が分かりました。
数式が適切でないから、「Formula」の設定が失敗し実行時エラーでとまるのだけど、そのときのrはブランクセルだったのでValueプロパティが「Empty値」となっているということですね。

おそらくですが、今回のケースは

 (1)３番目と最後のシートの少なくともどちらかのシート名が【数字で始まる、空白を含む、()などの記号を含む】等により「'」で囲む必要があった
 (2)しかし、「'」で囲まれておらず、シートが正しく参照できないので実行時エラーが発生した
 (3)↑の時点では当然、r.Valueに何も書き込まれてないので「Empty値」のまま

というのが真相じゃないでしょうか？
このとおりであれば、↓のように修正すればよいと思います。

    Sub test改()
        Dim r As Range

        For Each r In Selection
            r.Formula = "=SUM('" & Worksheets(3).Name & ":" & Worksheets(Worksheets.Count).Name & "'!" & r.Address(0, 0) & ")"
        Next r

    End Sub

（もこな２） 2021/05/24(月) 17:55

もこな2さんその通りです。（3月）(4月)(5月)シート名についていますし、選択範囲のセルに何も書き込まれてないセルが多いのでので「Empty値」のままでした。もこな2さん、ふむ～さん、Yさん、半平太さんご指導ありがとうございました。ご指導して頂いた一つ一つの教えを大切にしていきますので今後ともよろしくお願いいたします。
（昭久） 2021/05/24(月) 18:49

もこな2さん　さすがです。

質問者さんには、結果のみならず、
問題への対処の仕方（つまりデバッグ方法）こそに注目して、
そこをしっかりマスターしてください。
また別のエラーが出ても、ご自分で対応できるようにです。
2021/05/23(日) 23:16に書いたことを実行されていれば、ご自分で判明したはずです。

"=sum(" & mySum & r.Address(0, 0) & ")"
を変数に受けて、それをDebug.Printしたりして、
それを手作業でやってみれば、原因は自分でわかったはずです。

手作業でいくつかのセルをカーソル移動だけで合計する作業をしてみても分かるはずです。

# なお、レアケースですが、シート名に'が含まれていると、
# test改　でもまだエラーになります。
# 'を二つ続けてエスケープする必要があるからです。

（γ） 2021/05/24(月) 19:45

＞ 'を二つ続けてエスケープする必要があるからです。
確かにそうですね・・・・そこまで思い至りませんでした。
というわけで改善案。（ついでにSelectionされているセル範囲が１エリアならループ処理はいらないと思うのであわせて修正）

    Sub test改二()
        Dim buf As String

        buf = Worksheets(3).Name & ":" & Worksheets(Worksheets.Count).Name
        buf = Replace(buf, "'", "''")

        With Worksheets(1).Range("A1:D4")
            .Formula = "=SUM('" & buf & "'!" & .Cells(1).Address(0, 0) & ")"
        End With
    End Sub

（もこな２） 2021/05/25(火) 12:15

コメント返信：

[ 一覧(最新更新順) ]

[[20210523102830]] 『串刺し集計のvbaを習いましたが実行エラー1004が潤x（昭久） ページの最後に飛ぶ

[[20210523102830]]　『串刺し集計のvbaを習いましたが実行エラー1004が潤x（昭久）　ページの最後に飛ぶ