エクセルVBAで自動チャート作成。 エラーになります

質問

シート１に複数のデータが縦に入っています（A3:A90、B3:B90,C3:C90　といった具合です）データの数は３０個です。　それぞれのデータを個々のチャートにして別のシート（シート名: Chart1）に並べて表示させたいです。　それで以下の様なコードを書きました。　このコードはうまく動くのですが、同じようにシート２のデータをChart2のシートにチャートを出そうと思って、４行目の・・・Source:=Sheet1.Range(Cells(3, j)・・・のところをSource:=Sheet2.Range(Cells(3, j)にして実行するとエラーになります。
何故だか分からなくて困っています。　対処方法を教えてください。
エラーは Run-time error ‘1004’: Application-defined or Object-defined error　です。　お願いします。

For j = 1 To 30

Set ch = ActiveSheet.ChartObjects.Add(10, 5 + j * 105, 500, 100)
ch.Chart.ChartType = xlColumnClustered
ch.Chart.SetSourceData Source:=Sheet2.Range(Cells(3, j), Cells(90, j)), PlotBy:=xlColumns
ch.Chart.Location where:=xlLocationAsObject, Name:="Chart1"

With ch.Chart
.HasTitle = True
.ChartTitle.Characters.Text = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

Next j

回答No.4

taocat · ベストアンサー

再びこんにちは。NO.3です。
Sheet1でうまくいったということでしたのでちょっと考えてみましたら、分かりましたので追加情報です。

質問のコードはSheet1モジュールに書いてありますね。
なら話しがちょっと違います。
NO.3の回答は、コードをシートモジュールではなくて、標準モジュールに書いた場合です。

但し、Sheet1モジュールに書いた場合も問題点は、
Source:=Sheet2.Range(Cells(3, j), Cells(90, j))
これであり、回答は、
Source:=Sheet2.Range(Sheet2.Cells(3, j), Sheet2.Cells(90, j))
ですが、違いは

Cellsの前のシートオブジェクトを省略した場合、標準モジュールでは、常にActiveSheetのセルですが、シートモジュールに書いた場合は、常にそのシートです。
ですから、

Source:=Sheet2.Range(Cells(3, j), Cells(90, j))

これは、Shee2.rangeでSheet2を指し、
Cells(3,j),Cells(90,j)は常にSheet1を指していることになり、エラーが出るわけです。

それ以外はNo3の回答の通りです。
以上です。

お礼

回答有難う御座いました。
ご指摘のようにシート１にコードを書いていました。
いずれにしても、Range() の中の Cells前にシート名を指定しておかないとだめなんですね。　大変参考になりました。　回答いただいたコードでやってみます。

回答No.3

taocat

こんにちは。

先ず、疑問がひとつ。

＞それで以下の様なコードを書きました。このコードはうまく動くのですが

疑問はこれです。
これSheet1をActive、・・Source=Sheet1にして実行されたのですよね。
ほんどうにまともに動作しましたか？
J=1の時だけ動作して、J=2 でエラーが出たのではありませんか？

ここからが回答です。
提示のコードの問題個所は次のコードです。

Source:=Sheet2.Range(Cells(3, j), Cells(90, j))

このようにCellsの前にシートオブジェクトが省略されているとこのセルは常にActivesheetのセルということになってしまいます。
今回のように、Activesheetが変わる場合は拙いわけです。
Sheet2.RangeでSheet2を指しながら、Cellsの方は現在のActivesheetを指してることになります。

で、以下のように修正してください。そこを修正するだけでちゃんと動作するはずです。

Source:=Sheet2.Range(Sheet2.Cells(3, j), Sheet2.Cells(90, j))

のように各Cellsの前にシート名（コード名）を付けてどのシートのセルなのか明示します。
こうすることにより、Activesheetがどれなのか気にする必要もないし、For文の中でSheet2のSelectを繰り返す必要もありません。

一番のお薦めは、Chart２にグラフを描きたければFor文に入る前に、Chart2をAcitveにしておくことです。
理由は、使ってみたらお分かりになると思います。(^^;;;

因みにSheet2をActiveにしておいてから実行した場合、Chart2がActiveになるのは提示のコードでいうと
ch.Chart.Location where:=xlLocationAsObject, Name:="Chart1"
この時点です。

それから、
＞Source:=Sheet1.Range(Cells(3, j・・・のSheet1はシート名ではなく、アクティブになっているシートを表すと理解していいのでしょうか？

そんなことはありません。
Sheet1はあくまでSheet1です。シート名（コード名）です。そしてActiveかどうかも関係ありません。
そうでなければ、シート名を使う意味がないですよね。
試しに、シートChart2をActiveにして当方の回答の修正を加えたコードを実行してみてください。それも分かると思います。

以上です。

回答No.2

Wendy02

こんにちは。

正直なところ、私も、それについては分かりません。
＞Source:=Sheet1.Range(Cells(3, j・・・のSheet1はシート名ではなく、アクティブになっているシートを表すと理解していいのでしょうか？

私も同様の疑問には感じています。私の感覚でも、コンテナ形式(Sheet1.Range...)の書き方をした時には、Rangeオブジェクトは、明示的にシートの一点を指し示していますから、ブックのどこであっても見失うはずがありません。だから、エラーが出るはずがないように思うのです。ところが、エラーが出てきます。('1004'のいわゆる物理的エラーですね。)

コードを試してみたら、グラフを別の場所に移した時点で、シートが飛ぶので、だいたい、シートを　Select すれば解決する、というのは分かりましたが、説明はつきません。

だから、おっしゃるとおり「アクティブになっているシートを表すと理解していいのでしょうか？ 」と、そう考えざるを得ないのですね。よりよいコードはどうしたら良いかは考えるのですが、まったく考えがまとまりません。すみません、説明になっていませんね。

回答No.1

Wendy02

こんばんは。

最後の 行の間に、以下のように、Sheet2.Select を加入してください。

End With
Sheet2.Select
Next j

でも、画面がチカチカするので、

全体を、

Application.ScreenUpdating = False

'コード

Application.ScreenUpdating = True

で囲えばよいと思います。

補足

有難う御座います。　Sheet2.Select　を加入するだけで出来ました。　画面の切り替わりのチカチカも参考になりました。

　でも、意味がいまいちつかめないです。Source:=Sheet1.Range(Cells(3, j・・・のSheet1はシート名ではなく、アクティブになっているシートを表すと理解していいのでしょうか？