マクロ 計算式内の条件データを取得して計算をループ

質問

マクロ　計算式内の条件データを取得して計算をループさせたいのですが
上手くいかなかったので質問させていただきます。

　 [Sheet1] 　　　　　　　　 　　　　　　 　　 [Sheet2]
　 A 　　 　　B 　　　 C 　　 D 　　 　 　　 A 　 　　　 B 　 C 　 D
1 りんご　 　　　 　　　　　　　 　　　　　　　　　　　東京
2　　ばなな　　　　　　　　　　　　　　　　　　　　　　　◆
3　　みかん　　　　　　　　　　　　　　　　　
4　　ぶどう　　　　　　　　　　　　　　　　　
5　　なし　　　　　　　　　　　　　　　　　　　　　　　千葉　　　　　　　　　　　　　　　　
6　　…　　　　　　　　　　　　　　　　　　　　　　　　　　◆
7　　…
8　　…

上記表のようにsheet2のB1の東京ではSheet1のA2のばななが
Sheet1内のB・C・D列のそれぞれの条件でどれに当てはまるか
を返す計算式「=SUMIFS(Sheet1!$B$1:$B$5,Sheet1!$C$1:$C$5,B$1,Sheet1!$D$1:$D$5,$A2)」
を入れています。

次にSheet2のB5の千葉ではどうかといった具合に等間隔で情報を取得したいと
思っています。
このとき、上記計算式をコピーして貼り付けると「B$1」がそのまま反映され
Sheet2のB1の東京が指定されてしまいます。

うまく次の千葉を反映されるようにしたいのですが、可能なのでしょうか？

ちなみにループさせようとしたマクロです。

Dim ｒ As Integer
For ｒ = 1 To 5
Range("B2").Select
Selection.Copy
　　Worksheets("Sheet2").Cells(ｒ * 2 +　2, "B").Formula =
　　=SUMIFS(Sheet1!$B$1:$B$5,Sheet1!$C$1:$C$5,B$1,Sheet1!$D$1:$D$5,$A2)
Next ｒ

「B$1」の部分の絶対行参照をどうにかして計算式をループさせたいのですが
どうマクロを組んでいいのか分らなかったので質問させていただきます。

分りづらい説明ですみませんがよろしくおねがいします。

回答No.1

keithin · ベストアンサー

はて？
＞sheet2のB1の東京ではSheet1のA2のばななが

これが千葉のＢ６に行った時に、やっぱり千葉の「A2のばなな」でいいのか、それとも実はここも違う事をしたいのか、説明がヌケてます。
できなかったＢ６に「実際はこういう式で入ってほしい」と、ご相談では欲しい結果までキチンと正しく情報提供するようにしてください。

作成例：
sub macro1()
　dim r as long
　for r = 1 to 5
　　worksheets("Sheet2").cells(r * 4 - 2, "B").formular1c1 = _
　　"=SUMIFS(Sheet1!R1C2:R5C2,Sheet1!R1C5:R5C5,R[-1]C,Sheet1!R1C4:R5C4,RC1)"
　next r
end sub

お礼

やりたいことができました。
詳細な説明が抜けており伝わりにくくすみませんでした。