Excel VBAでクリックひとつでグラフの参照を隣の列へと移してグラフの変化を確認したいのですが

質問

縦にデータが70列ほど並んだエクセルシートに関して，そのうち1列目と2列目を参照したグラフを書いています．
Excel VBAでマクロをつくり，クリックひとつで2列目の参照が3列目へ，そしてもう一度クリックすると3列目の参照が4列目へと遷移させてグラフの変化を確認したいのですが，VBA初心者のためやり方がわかりません．

クリック1回でグラフの参照を1列ずらすようなマクロの作り方がわからなかったため，今は最後の列の隣（71列目）に2列目をコピーするようにして，グラフの参照は1列目と71列目にしています．
しかしこのようにしてもRange内に変数を入れてはいけないせいなのか，エラー（Rangeメソッドは失敗しました:Globalオブジェクト）と表示されて全く動きません．
現在書いているコードは以下のとおりです．

Sub change_graph()
'
' change_graph Macro
' マクロ記録日 : 2009/12/15 ユーザー名 : ABC
'

'
Dim cCnt As Long
Dim myAddress As String

ActiveCell.Offset(0, 1).Range("A1:A914").Select
myAddress = ActiveCell.Address
cCnt = Range("& myAddress:$W$61").Columns.Count
Selection.Copy
ActiveCell.Offset(0, cCnt - 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -(cCnt - 1)).Range("A1").Activate

End Sub

よろしければ
（１）そもそもグラフの参照を1列ずつ変化させるやり方
（２）上記コードの誤り
を教えていただけないでしょうか？
よろしくお願いいたします．

回答No.2

xls88 · ベストアンサー

(1)
下記のようなコードで、マクロ実行の度に、元のデータが1列オフセットします。
グラフを選択しておいてマクロを実行してみてください。

上手くいかない場合、プロットされている棒？（あるいは折れ線？）を選択し、数式バーに表示されるSERIES式を教えてください。

あと、最終列がプロットされている場合はどうしますか？
最初の列に戻る？
それともそのままマクロを終了する？

Dim fomla As Variant
Dim ws As String

fomla = Split(ActiveChart.SeriesCollection(1).Formula, ",")
ws = Split(Split(fomla(0), "(")(1), "!")(0)

With ActiveChart.SeriesCollection(1)
.Name = "=" & ws & "!" & Range(Split(fomla(0), "(")(1)).Offset(, 1).Address
.Values = Sheets(ws).Range(fomla(2)).Offset(, 1)
End With

最終的にグラフオブジェクトに「マクロの登録」すればどうでしょうか。
グラフをクリックでマクロが実行されるようになると思います。
その場合少し手直しが必要です。

(2)
目についたところ
未確認ですが
cCnt = Range("& myAddress:$W$61").Columns.Count
は
cCnt = Range(myAddress & ":$W$61").Columns.Count
でどうなるでしょうか。

お礼

ご回答ありがとうございます．

（２）に関して，修正してみたところ完成しました．ありがとうございます．

（１）に関して，散布図を選択したときに数式バーに表示されるSERIES式は以下のとおりです．

=SERIES(,シート名!$A$61:$A$974,シート!$B$61:$B$974,1)

教えていただいた数式の内容をまだ理解できていないためそのままコピーアンドペーストしたのですが，

実行時エラー'91':
オブジェクト変数またはWithブロック変数が設定されていません

と表示されてエラーになってしまいます．どこを修正すればこのコードが有効になるかを教えていただけると幸いです.

回答No.5

end-u

もし、グラフを選択していても同じエラーが発生する場合、シートが１つしかない状況かもしれません。
実は2003以前では１シートのみのシート上グラフのSERIES関数は、[Book名.xls]が付いてるんです。
（数式バーには出てこない。Formulaプロパティで取得した場合）
いずれにしても、Range(fomla(2))でシート名込みでRagneを指定してますから、Sheets(ws).　が不要になります。

ActiveChart.SeriesCollection(1).Values = Range(fomla(2)).Offset(, 1)

回答No.4

xls88

＞回答番号：No.2 この回答へのお礼
下記内容についての回答を忘れていました。

＞実行時エラー'91':
＞オブジェクト変数またはWithブロック変数が設定されていません
マクロを実行する前に、グラフクリックして選択されていますか？
回答番号：No.2で記述しておいたのですが伝わっていないようです。

回答No.3

xls88

=SERIES(,シート名!$A$61:$A$974,シート!$B$61:$B$974,1)
は散布図なら
=SERIES(系列名,Xの値,Yの値,系列番号)
といった内容になります。
系列名が設定されていないようです。
コードは下記のようにすれば良いと思います。

Dim fomla As Variant
Dim ws As String

fomla = Split(ActiveChart.SeriesCollection(1).Formula, ",")
ws = Split(fomla(2), "!")(0)

With ActiveChart.SeriesCollection(1)
.Values = Sheets(ws).Range(fomla(2)).Offset(, 1)
End With

回答番号：No.1 end-uさんの回答内容も面白そうですが試されましたか？

お礼

はじめの回答内容はちょっとやり方がわからずにそのままにしてしまっています．
まだコードが理解できていないのですが無事動きました！
ありがとうございます！

回答No.1

end-u

>（２）上記コードの誤り
cCnt = Range(myAddress & ":$W$61").Columns.Count
です。
"& myAddress:$W$61"　では、『　& myAddress:$W$61　』という文字列が Range(　) の中に入ってしまいます。
変数 myAddress はStringですから、例えば "$B$1" という文字列がはいっていたら、
myAddress & ":$W$61" とする事で、 Range("$B$1:$W$61") になります。

>（１）そもそもグラフの参照を1列ずつ変化させるやり方
いろいろやり方はありますが、例えばマクロを使わず、数式で処理する方法も考えられます。
グラフ元データの範囲を２行目から始めて、A1セルを作業用セルとします。
A列は固定のようですから、C列以降に可変させたいデータを配置します。
A:B列でグラフを作成して、B列には
B2：=INDEX(C2:Z2,$A$1)
以下コピー。
こんな感じの式を入れておけばA1セルの値を変更する事でグラフの系列は変えず、数値を遷移させる事ができます。
マクロでやりたいなら、A1セルの値を書き換えるマクロにすると良いでしょう。