ExcelVBAマクロコードの「実行時エラー」の理由への質問です。

以下のマクロコード(正常に動作する)が有ります。
Sub MACRO1()
Sheets(2).Range("B12:Z18").Copy _
Sheets(1).Range("BX12")
End Sub
　所が、上記のセルアドレス設定を[A1形式]から、下記の通り[R1C1形式]に変更したら、
「実行時エラー」になるのは、どうしてでしょうか？
Sub MACRO1()
Sheets(2).Range(Cells(12, 2), Cells(18, 26)).Copy _
Sheets(1).Range(Cells(12, 76))
End Sub
　「エラーの理由と[R1C1形式]を利用した正解コード」を教えて戴ければ助かります。
よろしくお願いいたします。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2020/05/15 16:32

No2です

＞「Worksheets("Sheet2").Range(Cells(12, 2), Cells(18, 26)).Copy」
＞の記述に[問題があるのでは？]と思いましたが、
多分、原因がわかりました。

エラーになる時は、Sheet2がアクティブになっていないものと推測します。
Sheet2をアクティブにしておいて、同じコードを実行すれば動作すると思います。
…というか、そのコードはSheet2がアクティブなことが前提ではないのですか？

Range ( cell1, cell2 ) 　形式の場合、オブジェクトにブック、シートの指定が無ければデフォルトのブック、シート（＝ThisWorkBook、ActiveSheet）と見做して解釈されます。
ご提示のコードの場合は、Rangeには明示的にSheetの指定がされていますが、引数のCellsには指定がないのでActiveSheetとして解釈されます。
この時に、Rangeで指定しているシートとActiveSheetが異なっていると、意味的に矛盾が生じるので、エクセルは「解釈不能」としてエラーを吐きます。
おそらく、（↑）のような事象が発生しているのではありませんか？

解決は簡単で、「同じシートを指定しておく」だけです。
（意味的にも当然そのはずなので）
そのまま記述すると長い記述になりやすいので、例えば、Withブロックを利用するとか、あらかじめ変数にしておくなどの方法がとられていると思います。

また、No2で述べた
＞　　Set X = ~~Range()
＞　　Set Y = ~~Range()
＞　　Ｘ.Copy Destination:=Y
はお試しになってはいないようですが、このように記述してみれば,
　Set X = ~~Range()
でエラーが発生することから、Copyの記述に問題があるのではなく、セル範囲の指定に問題があることが即座に見て取れるはずと推測します。

この回答へのお礼

度々のご回答、大変ありがとうございました。
ご指摘の通り
>Range ( cell1, cell2 ) 　形式の場合、オブジェクトにブック、シートの指定が無ければデフォルトのブック、シート（＝ThisWorkBook、ActiveSheet）と>見做して解釈されます。
>ご提示のコードの場合は、Rangeには明示的にSheetの指定がされていますが、引数のCellsには指定がないのでActiveSheetとして解釈されます。
>この時に、Rangeで指定しているシートとActiveSheetが異なっていると、意味的に矛盾が生じるので、エクセルは「解釈不能」としてエラーを吐きます。
>おそらく、（↑）のような事象が発生しているのではありませんか？
　小生のコード記載は[ThisWorkBook]上で、ボタン設置は[Sheet1]上でした。
　ご指摘に従って、先ずは[Sheet2]をアクティブにして実行すべく、取り敢えず[ThisWorkBook]上のコードを[Sheet2]に移して、実行したら・・・
見事！にエラー無く実行できました。ありがとうございました。A1形式とCells(R,C)形式の変換には注意が必要なのですね。
それから、前回のお礼(報告)に記載したと思いましたが、「No2でお示しのSet X =~~Range・・・」も試しました。結果は仰った通りの結果でした。
(しかし、小生には「エラー内容が同じ」だったので、エラーの区別の検知するものとの認識が無く、キチンンと報告せずに失礼しました)
　最後まで、丁寧にご回答でお付き合い戴き、本当にありがとうございました。

お礼日時：2020/05/16 08:01

No.2 回答者： fujillin 回答日時： 2020/05/15 12:34

No1です

＞さらに、ご検討戴ければ助かります。
当方でテストしたところでは、以下で問題なくコピーされます。
（シート名などが異なるのはテストしたシートが異なるためです）
Worksheets("Sheet10").Range("B12:Z18").Copy _
　　Destination:=Worksheets("Sheet1").Cells(12, 76)

一体、どのような状態で実行して、どのようにエラーになるのでしょうか？
試しにセル範囲を一旦変数に代入して
　　Set X = ~~Range()
　　Set Y = ~~Range()
　　Ｘ.Copy Destination:=Y
のような3行に分解すると、どの行でどのようなエラーになりますか？

この回答へのお礼

こんにちは　早速の第二報、恐縮です。ありがとうございます。
　さて、小生の実行状態ですが、背景を含めた細かい格子状のセルに背景色で作成した絵(1コマ)をFor－Next分でコマを変化させるコードです。
その一コマのコピペのコードです。(「VBAマクロはボタンに登録して実行する」様になっています)
　ご回答いただいたコードで試しても結果は同じ・・・でした。(前回お礼(報告)で述べたようなエラーメッセージになります)
そこで、回答にも示されたRange(”A1形式”)コードとCells(R,C)を混在したコード(４ケース)で試してみて判明したと思われる事を報告いたします。
　Worksheets("Sheet10").Range("B12:Z18").Copy _
　　Destination:=Worksheets("Sheet1").Cells(12, 76)
は確かに動作しました。
しかし、質問のコードだけでなく、以下のコードでも同じ様なエラーメッセージになりました。
　Worksheets("Sheet2").Range(Cells(12, 2), Cells(18, 26)).Copy _
　　Destination:=Worksheets("Sheet1").Range("BX12")
どうやら、
　「Worksheets("Sheet2").Range(Cells(12, 2), Cells(18, 26)).Copy」の記述に[問題があるのでは？]と思いましたが、
「何故か？」は小生では判りません。(違う原因かもしれませんが・・・)
小生の質問の趣旨である「Range("A1形式")をRange(Cells(R,C), Cells(R,C))に変更すると、何故エラーになるのか？解決策はあるか？」について、
　お手数をおかけしますが、ご教示の程、よろしくお願いいたします。

お礼日時：2020/05/15 15:58

No.1 回答者： fujillin 回答日時： 2020/05/15 09:54

こんにちは

単にRangeオブジェクトの指定方法の間違いではないでしょうか？
以下のサイトより抜粋
＞Range オブジェクトを返すには、Range ( cell1, cell2 ) プロパティを使用します。
https://docs.microsoft.com/ja-jp/office/vba/api/ …
Range ( cell1)という指定方法はありません。

＞「エラーの理由と[R1C1形式]を利用した正解コード」
[R1C1形式]という単語を誤解なさっているようです。
ここで説明はしませんが、調べておいた方が良いでしょう。

動作するコードの一例として、destinationに
　Sheets(1).Cells(12, 76)
で足りるかと思います。

この回答へのお礼

早速の解答返信に恐縮します。大変ありがとうございます。
>Range ( cell1)という指定方法はありません。
なのですね。
　しかし、解答例に従って修正しても、同じようなエラーメッセージが表示されます。
エラーメッセージは(1)「✕　400」(2)デバックモードのステップｉｎだと「実行時エラー　オブジェクト定義エラー・・・」と出ます。

　実は、小生の質問時には記載してなくて申し訳ございませんでしたが、
(小生も初めは「Sheets(1).Cells(12, 76)」コードでダメなので、Range()で囲みでもダメで、それを投稿しました）
キチンと記載せずに失礼しました。

　さらに、ご検討戴ければ助かります。

お礼日時：2020/05/15 11:27