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

Question

縦にデータが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列ずつ変化させるやり方
（２）上記コードの誤り
を教えていただけないでしょうか？
よろしくお願いいたします．

xls88 · Accepted Answer

(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
でどうなるでしょうか。

end-u · Answer

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

xls88 · Answer

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

xls88 · Answer

=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さんの回答内容も面白そうですが試されましたか？

end-u · Answer

>（２）上記コードの誤り
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セルの値を書き換えるマクロにすると良いでしょう。

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

(1)

もし、グラフを選択していても同じエラーが発生する場合、シートが１つしかない状況かもしれません。

＞回答番号：No.2 この回答へのお礼

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

>（２）上記コードの誤り

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング