【ExcelVBA】複数の変数をセットで変えていく方法を教えてください。

質問

VBA初心者なので非常に初歩的な質問ですみません。
複数列に入った計算式を何セットかコピーして、計算式の内容を置換していきたいのです。
８セットくらいなのでベタで書いてもいいのですが、簡潔にまとめる方法があったら教えてください。
初心者的なVBAですが、コピーは以下のように書きました。
※作成したい計算列は８個（元を入れて）
※元の計算式は AD:AM　の列に入っています。
Sub Macro1()
Dim cx As Variant
Dim C1 As Variant
Dim C2 As Variant
Dim C3 As Variant
Dim C4 As Variant
Dim C5 As Variant
Dim C6 As Variant
Dim C7 As Variant
Dim C8 As Variant
C1 = "AD:AM"
C2 = "AO:AX"
C3 = "AZ:BI"
C4 = "BK:BT"
C5 = "BV:CE"
C6 = "CG:CP"
C7 = "CR:DA"
C8 = "DC:DL"
Columns(C1).Select
Selection.Copy
For Each cx In Array(C2, C3, C4, C5, C6, C7, C8)
Columns(cx).Select
ActiveSheet.Paste
Next
End Sub
このあとにコピーした計算式に含まれる列名を置換します。
ベタで書くと以下のようになります。
Columns(C2).Select
Selection.Replace What:="$G", Replacement:="$J", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="$F", Replacement:="$I", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
C2の範囲では$G⇒$J、$F⇒$Iと置換します。
$Gと$Fは固定です。
置換する列名は以下の通りです。
$G⇒"$J", "$M", "$P", "$S", "$V", "$Y", "$AB"
$F⇒"$I", "$L", "$O", "$R", "$U", "$X", "$AA"
C2～C8の範囲で上記が変化していきます。
この「セットで変化」がどうやっても解決しません。
初歩的な質問で大変申し訳ないのですが、よろしくお願いいたします。

回答No.2

KenKen_SP · ベストアンサー

こんにちは。

> ベタで書くと以下のようになります。

この程度の件数なら悩むより、ベタに書いた方が話は速いですよ。

> この「セットで変化」がどうやっても解決しません。

まとめて１回で置換ということはできません。繰り返し置換するだけです。
一例です。

列全体をコピー貼り付けというのは強引過ぎる気がしますけども...

Sub TestProc()

　　Dim r　　　　 As Range
　　Dim vReplaceG As Variant
　　Dim vReplaceF As Variant
　　Dim i　　　　 As Long

　　Const COL_SRC = "AD:AM"　　　　　　　　　　　　　　　　　　 ' // 数式ソース範囲
　　Const COL_LST = "AO:AX,AZ:BI,BK:BT,BV:CE,CG:CP,CR:DA,DC:DL" ' // 数式貼り付け先

　　vReplaceG = Split("$J,$M,$P,$S,$V,$Y,$AB", ",") ' // $G 変換リスト
　　vReplaceF = Split("$I,$L,$O,$R,$U,$X,$AA", ",") ' // $F 変換リスト

　　i = 0
　　For Each r In Range(COL_LST).Areas
　　　　Range(COL_SRC).Copy Destination:=r
　　　　r.Replace What:="$G", Replacement:=vReplaceG(i), LookAt:=xlPart
　　　　r.Replace What:="$F", Replacement:=vReplaceF(i), LookAt:=xlPart
　　　　i = i + 1
　　Next

End Sub

お礼

回答ありがとうございます。
おっしゃる通りベタで書いても問題ないのですが、
同じ様なことをよくやるので、どうにかこの機会に方法を見つけて
見たかったのです。
（結局自分で解決できずお願いしてしまいましたが）
おかげさまで解決です。
非常に勉強になりました。ありがとうございました。

回答No.1

Wendy02

こんにちは。

ざっと見た感じですが、

最初のマクロは、

Sub MacroTest1()
　Columns("AD:AM").Copy _
　Range("AO:AX,AZ:BI,BK:BT,BV:CE,CG:CP,CR:DA,DC:DL")
End Sub

ということだと思います。

C2を含めて変換していくとして、絶対参照でこうなっているとすれば、

$G⇒"$J", "$M", "$P", "$S", "$V", "$Y", "$AB"
$F⇒"$I", "$L", "$O", "$R", "$U", "$X", "$AA"

以下のようになると思います。

Sub MacroTest2()
Dim r As Range
Dim i As Long
Set r = Range("C2:C8")　'範囲
For i = 1 To r.Count
　r.Cells(i).FormulaR1C1 = Replace(r.Cells(i).FormulaR1C1, "C7", "C" & 7 + i * 3)
　r.Cells(i).FormulaR1C1 = Replace(r.Cells(i).FormulaR1C1, "C6", "C" & 6 + i * 3)
Next i
Set r = Nothing
End Sub

お礼

回答ありがとうございます。
そもそも複数範囲へのコピーがこれほどあっさりできるとは・・・。
本当に素人ですみません・・・。
コピーのほうはできたのですがtest2()は、何が起きたのかわからない
くらいのあいだに終了してしまって何もおきません・・・。（涙）
内容はなんとなくわかるので（なんとなくのレベルですみません）
自分でやってみます。ありがとうございました。