Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
セル書式の範囲指定で1004アプリケーション定義またはオブジェクト定義のエラー
投稿日時: 17/12/30 16:06:07
投稿者: ビタミンVBA

いつもお世話になります。
 
ワークシートで数個のセル範囲をまとめてセル書式を設定しようとすると上記のエラーが出ます。
似た書式で設定してもエラーにならないシートもあります。
 
(1)2つのシートがあり、シート1にコマンドボタンを2つ配置し、ボタン1ではシート1に
例えば2行目の"A"列〜"R"列に適当な項目名を記述し、セル書式を「折り返して表示」「中央に配置」
のために

With Worksheets("Sheet1")
 .Activate
 .Cells(2,"A").Value="項目1"
・・・(中略)
 .Cells(2,"R").Value="項目XX"

 With .Range(Cells(2,"A"),Cells(2,"R")) ←まとめて範囲指定(OK)
   .WrapText=True
   .HorizontalAlignment=xlCenter
 End with
End With

 
のように記述し、正常に設定できます。
 
(2)しかし、2つ目のボタンに類似の設定をして実行するとエラーになります。
"Sheet2"では項目数が多いのと、100行置きに同じ項目を繰り返し表示するために
セル位置を変数で指定しています。
 
For p=1 to 10
  With Worksheets("Sheet2")
   .Activate
   .Cells(2+100*(p-1),"A").Value="項目1"
   ・・・(中略)
   .Cells(2+100*(p-1),"AO").Value="項目YY"

   With .Range(Cells(2+100*(p-1),"A"),Cells(2+100*(p-1),"AO"))  ←ここでエラー1004
     .WrapText=True
     .HorizontalAlignment=xlCenter
   End with
  End With
Next p

1行目のみのチェックのために"Sheet1"のまとめ部分をコピーして試してもエラーになります。
(Sheet1ではOKでもSheet2ではエラー)
 
ただ、各項目でセル書式をすると個別では正常です。
With .Cells(2+100*(p-1),"A")
   .Value="項目1"
   .WrapText=True
   .HorizontalAlignment=xlCenter
 End With
・・・(中略)
 With .Cells(2+100*(p-1),"AO")
   .Value="項目YY"
   .WrapText=True
   .HorizontalAlignment=xlCenter
 End With
のようにするとエラーにならずに設定できます。
 
2つのマクロの違いは1つ目は同じシート内での処理、2つ目は別のシートへの処理と
項目数の違い(変数による複数処理)ですが、基本的には同じ書式設定の流用です。
別のシートから別のシートを呼び出し、似たような設定をするのは普通に
やっており、他のマクロでも同様な処理をしていると思いますが、こういう
エラーに遭遇したか記憶があいまいです。
 
タイプミスなどではなく、何か基本の理解がおかしいのでしょうが、わかりません。
アドバイスをよろしくお願いします。

回答
投稿日時: 17/12/30 16:16:40
投稿者: WinArrow
投稿者のウェブサイトに移動

この掲示板へコードを投稿する場合、
掲示板に手入力ではなく、コードペインからコピペしてください。
 
実際のコードを掲示して貰わないと、正確な診断ができません。
 

回答
投稿日時: 17/12/30 16:44:14
投稿者: WinArrow
投稿者のウェブサイトに移動

(2)で掲示したコードと
>ただ、各項目でセル書式をすると個別では正常です。
の後のコードは、考え方が違います。
 
どちらが本当なのか?
掲示板に手入力しているから・・・正確な情報ではないですね?
 
>タイプミス
 
VBE画面でのタイプミスもあれば、掲示板へのタイプミスもあるからね・・・
過信しないこと。

回答
投稿日時: 17/12/30 16:57:44
投稿者: WinArrow
投稿者のウェブサイトに移動

質問に対する回答ではないが、
 
プログラムをシンプルにするために、↓のような記述もありますよ
 
@質問者さんの記述と同じ思想
    For p = 1 To 10
        Debug.Print Cells(2 + 100 * (p - 1), "A").Address
    Next
Aシンプル化
    For p = 2 To 10 * 100 Step 100
        Debug.Print Cells(p, "A").Address
    Next
コメント
@は、行位置を10回計算している
に対し
Aは、行位置は計算しないが、ループ終了条件で1回の計算
 
ループが10回くらいだと殆ど処理時間の差はないが、
ループ回数が何万回となると処理時間に差がでてきます。
 
 

回答
投稿日時: 17/12/30 18:17:49
投稿者: simple

詳しく見ていませんが、こういうことですか?
>With .Range(Cells(2+100*(p-1),"A"),Cells(2+100*(p-1),"AO")) ←ここでエラー1004
中のCellsにもシートを指定するのが正しいです。

With .Range(.Cells(2+100*(p-1),"A"),.Cells(2+100*(p-1),"AO"))

投稿日時: 17/12/30 19:16:51
投稿者: ビタミンVBA

WinArrowさん、simpleさん、さっそくのコメントありがとうございます。
 
>WinArrowさん
実際のコードでなくてすみませんでした。実際のコードを簡略化したものでもエラーになったので再度
載せようかと思いましたが、simpleさんのコメントの通りにしたら正常に動作しましたのでとりやめます。
(それはそれで疑問が残るのですが・・・)
 

引用:
@は、行位置を10回計算している
 に対し
Aは、行位置は計算しないが、ループ終了条件で1回の計算
  
ループが10回くらいだと殆ど処理時間の差はないが、
ループ回数が何万回となると処理時間に差がでてきます。

 
実際に使うのも10数回程度なので実用上の問題はないですが、今までいつも@の方法でしたので
Aの方法も今後考えてみます(@の方が自分には直感的にわかりやすいのですが、速度面は考えていませんでした)
 
 
>simpleさん、
With .Range(.Cells(2+100*(p-1),"A"),.Cells(2+100*(p-1),"AO")) とCellsの前にピリオドを入れたら
正常に動作しました。
以前(現在)他の部分で表全体をRangeで扱う際に.Rnage(.Cells(X,X),.Cells(Y,Y))のようにすると
実際の位置が明後日の箇所に飛んでしまうので、ピリオドなしで使う方法にしてました。こういう理解が
あやふやなまま使っていますが、またアドバイスを基にいろいろ試してみます。
 
本件はこれで解決とさせていただきます。ありがとうございました。
またよろしくお願いします。