Excel VBA マクロ処理 リンク先参照変更方法

質問

Excel VBA マクロ処理 リンク先参照変更方法で質問です。

データがあるsheet名を『データ』、
平均を集計するsheet名を『平均集計』とします。

行いたい処理は
『データ』にある【　】内の５コのデータの平均値をまとめたい。
『平均集計』に平均値を集計してまとめる。
5コデータの平均値を既に参照指定済のsheetを使用します。

***『データ』詳細**********
縦軸【1×5】×25項目 = 130行
横軸に13列 A～M列
**************************

■教えて欲しい処理部分はここ！
『平均集計』リンク参照先を一気に変更させたい。

ActiveCell.FormulaR1C1 = "=AVERAGE('リンク参照'!R[★]C:R[★]C)"
↑上記のリンク参照先を一気に変更させる方法はありますでしょうか？
例＞
ActiveCell.FormulaR1C1 = "=AVERAGE('リンク参照先変更'!R[★]C:R[★]C)"
のように…。

あと補足で、教えて欲しいのですが…
★データが追加された場合 例＞5 → 7
この時行う処理方法がよくわかりません…。

単発だと
ActiveCell.FormulaR1C1 = "=AVERAGE('リンク参照先変更'!R[1]C:R[7]C)"
で可能ですが…

↓　複数になると…　
x=1
y=7
FOR i=1 to 25
ActiveCell.FormulaR1C1 = "=AVERAGE('リンク参照先変更'!R[x]C:R[y]C)"
x=y+1
y=y*(i+1)
NEXT
…エラーになりました。
追加処理の場合、どのような方法がありますでしょうか？

以上、アドバイスよろしくお願い致しますm(__)m

回答No.2

Wendy02

こんにちは。

私は、ご質問を良く理解しているわけではないけれども、A1方式をお使いなら、FormulaLocal でやってみたらいかがですか？

＞『平均集計』リンク参照先を一気に変更させたい。
これって、編集－置換で出来ませんか？マクロということもないと思います。

＞補足で、教えて欲しいのですが…

最初に、Range(Cells(3, 1), Cells(9, 1))　または、Range("A3:A10") でも可
つまり、最初の「ものさし」です。

それから、本来、ActiveCell というのは、可変ですから、位置決めしたほうが安全です。あまり、記録マクロみたいな書き方をすると、返ってややこしいです。

後は、こんなことかなって思います。

Sub TestSample()
　 Dim rng As Range, i As Long, j As Long

　 '---------------------------------------
　 ''要設定　'目的のシートの最初の範囲のアドレス
　 Set rng = Range(Cells(3, 1), Cells(9, 1))
　 '---------------------------------------

　 ''ActiveCell の位置決めが必要 以下に設定
　 'Range("A1").Select

　 For i = 0 To 24 '最初を含めるので1を引く
　　　For j = 0 To 12 '1を減らす

　　　　 ActiveCell.Offset(i, j).FormulaLocal = "=Average(リンク参照先変更!" & rng.Offset(i * 5, j).Address(0, 0) & ")"

　　　Next j
　 Next i
End Sub

お礼

お礼遅れましてすみません…。
>これって、編集－置換で出来ませんか？マクロということもないと思います。
ごもっともです。この方法が一番手っ取り早そうです。
今後この手法を活用しようと思います。
マクロ処理のご回答もありがとうございました！ m(_ _)m

回答No.1

papayuka

こんな感じ？

Sub Test1()
Dim r1 As Range, r2 As Range
　On Error Resume Next
　Set r1 = Application.InputBox("現在の参照先のセルを１つクリック", "現在", Type:=8)
　Set r2 = Application.InputBox("新しい参照先のセルを１つクリック", "変更後", Type:=8)
　Worksheets("平均集計").Cells.Replace What:=r1.Parent.Name, Replacement:=r2.Parent.Name, LookAt:=xlPart
End Sub

Sub Test2()
x = 1
y = 7
　For i = 1 To 25
　　ActiveCell.FormulaR1C1 = "=AVERAGE('リンク参照先変更'!R[" & x & "]C:R[" & y & "]C)"
　　x = y
　　y = x + 7
　　Activecell.Offset(1,0).Activate
　Next i
End Sub