エクセルVBAマクロで条件付き合計の方法

質問

Bに銀行名　a銀行　b銀行・・・
E F G H I Jにデータ末尾変動の数値（金額）が記入されており、
For i% = 1 To 6
adr2$ = Range("e7").Cells(1, i%).Address
Range(adr2$).Cells(DatNum + 3, 1).Formula = "=SUMIF(B7:B1000,""a銀行"",E7:E1000)"
と記入してa銀行の合計金額を記入しています。
しかし、上記記述ではEからセルが横に移動しても全てE7:E1000の合計となり意味がありません。

そこで、eのデータ末尾＋３にE7～データ末尾
　　　　　fのデータ末尾＋３にF7～データ末尾
以下順番にforの条件が完了するまで
としたいのですが、どのように書き換えればよろしいでしょうか。
逐一対応セルに標記するようにマクロを記述する方法もあるとはおもいますが、簡潔にできるのではないかと思い質問させて頂きました。

回答No.3

keithin · ベストアンサー

はて……？？？？
＞データ末尾＝各列最終行ではございません。
＞値が入力されているセルの一番下ということです。

回答２のマクロは「値が入力されているセルの一番下」の＋３行に数式を記入するよう，作成してありますが，いったいどういうことでしょうか。
回答２のマクロを実際に動かして，いったい「どのように違っている」（具体的に。今のアナタのエクセルで回答のマクロを動かしたら，ここの番地にこういう数式が入ったけど，これこれの理由でここの番地にこういう内容で数式が入って欲しい，と）のか，キチンと教えてください。

そもそも。
＞各列毎に設定する方法ではなく、まとめて設定できないものかと思い質問させて頂いております。

いまＥ列はＥ８０までデータが記入されていて，Ｅ８３に数式を入れたい
いまＦ列はＦ１０３までデータが記入されていて，Ｆ１０６に数式を入れたい
　：
いまＪ列はＪ３４５までデータが記入されていて，Ｊ３４８に数式を入れたい

それらのＥ８３，Ｆ１０６…Ｊ３４８に，まとめて数式を入れたい，とそういう事を言ってるように見えますが，そういう事でしょうか？
やれば勿論出来ますが，なんとも無意味なコダワリにしか思えません。

それとも？
アナタの用意した正体不明のdatnumの値（仮に４２３とか）を信頼して，Ｅ列：Ｊ列の実際のデータの入り具合はもう見る必要は無くて，datnum＋３行に（たとえばＥ４２６：Ｊ４２６に）まとめて一斉に数式を記入したいと言いたいのでしょうか。

こちらも実際に試してください：
sub macro2()
　dim datnum
’アナタが用意したdatnumの値の取得をここに書き写す

　cells(datnum, "E").resize(1, 6).formular1c1 = "=SUMIF(R7C2:R[-3]C2,""a銀行"",R7C:R[-3]C)"
end sub

回答１然り，回答２も同じですが，それぞれ回答で寄せられたマクロを実際に試してから「アナタが書いてくれたマクロを実際に動かしてみたら，ここが違うのでこうしたい」という具合に，もうちょっとキャッチボールしてみるのもいいんじゃないでしょうか？

お礼

質問の仕方、返答の仕方につきまして失礼を重ね申し訳ございません。

No.2のマクロを実行したところ、確かに最終行+3ではあるのですが
合計
a銀行
b銀行
と既にマクロにて先に標記していますので、その更に３列下に表示され、
合計
a銀行
b銀行
1列目
2列目
3列目　合計値　合計値・・・
という形になってしまい、希望とは違うものだと判断をしてしまっておりました。

この度のご指摘を受けた後+3の数値を＋１や＋２に変えて銀行名を変更していけば対応できることに気がつき、現在は希望に添った形になっております。

For c = 5 To 10
r = Cells(65536, c).End(xlUp).Row
If r >= 7 Then
Cells(r + 1, c).FormulaR1C1 = "=SUMIF(R7C2:R[-3]C2,""a銀行"",R7C:R[-3]C)"
End If
Next c
For c = 5 To 10
r = Cells(65536, c).End(xlUp).Row
If r >= 7 Then
Cells(r + 1, c).FormulaR1C1 = "=SUMIF(R7C2:R[-3]C2,""b銀行"",R7C:R[-3]C)"
End If
Next c
と記述させていただき、使わさせて頂きます。
範囲がそのうち合計やa銀行などに被さってきますが、Bを銀行名検索なのでこだわらずにいこうと考えております。
もし、このまま数が増えると問題が起こる可能性がありますようでしたらご教授頂ければ幸いです。

>いまＥ列はＥ８０までデータが記入されていて，Ｅ８３に数式を入れたい
>いまＦ列はＦ１０３までデータが記入されていて，Ｆ１０６に数式を入れたい
>　：
>いまＪ列はＪ３４５までデータが記入されていて，Ｊ３４８に数式を入れたい
>
>それらのＥ８３，Ｆ１０６…Ｊ３４８に，まとめて数式を入れたい，とそういう事を>言ってるように見えますが，そういう事でしょうか？
>やれば勿論出来ますが，なんとも無意味なコダワリにしか思えません。
こちらにつきましては、私の説明がわるかったことが招いた誤解でありますことをお詫び申しあげます。

>sub macro2()
>　dim datnum
>’アナタが用意したdatnumの値の取得をここに書き写す
>
>　cells(datnum, "E").resize(1, 6).formular1c1 = "=SUMIF(R7C2:R[-3]>C2,""a銀行"",R7C:R[-3]C)"
>end sub
こちらのマクロにつきましても描いていたものと同じ作業ができました。

色々と作法につきましてもご教授頂き、感謝致しております。
この度は色々とご無礼が有りましたことをこの場をかりましてお詫び申しあげます。
この度は本当にありがとうございました。

回答No.2

keithin

数式を記入するのは，各列の最下行＋３行目でいいんですね？（アナタが掲示したマクロは，そうは書かれていませんが，マクロの方を無視します）

作成例：
sub macro1()
　dim c as integer
　dim r as long
　for c = 5 to 10
　　r = cells(65536, c).end(xlup).row
　　if r >= 7 then
　　　cells(r + 3, c).formular1c1 = "=SUMIF(R7C2:R[-3]C2,""a銀行"",R7C:R[-3]C)"
　　end if
　next c
end sub

お礼

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

補足

当方の記述がおかしかったことをお詫びさせて頂きます。
データ末尾＝各列最終行ではございません。
値が入力されているセルの一番下ということです。
100行目まで値があれば103行目にa銀行の合計
200行目まで値があれば203行目にa銀行の合計
という形です。
最終行が変動しますがDatNumが入力値の最終行になるように設定してあります。
E列からJ列の入力値の最終行+3行目にBがa銀行なら合計をする
という形をとりたいと思っております。

各列毎に設定する方法ではなく、まとめて設定できないものかと思い質問させて頂いております。

非常に言葉足らずな質問で申し訳ありませんでした。

回答No.1

mu2011

一例です。
Range(adr2$).Cells(datnum + 3, 1).FormulaR1C1 = _
"=SUMIF(r7c2:r1000c2,""a銀行"",r7c:r1000c)"

お礼

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

補足

ご返事ありがとうございます。
Range(adr2$).Cells(datnum + 3, 1).FormulaR1C1 = _
"=SUMIF(r7c2:r1000c2,""a銀行"",r7c:r1000c)"
これですと
Range(adr2$).Cells(DatNum + 3, 1).Formula =　_
"=SUMIF(B7:B1000,""a銀行"",E7:E1000)"
を絶対値形式で書き直しただけで解決に至っていないと思われるのですが当方の勘違いでしょうか？

この方法でも各セルに対応するように値をずらして行けばできるのですが、コードが長くなりすぎるのでまとめれないかと考えております。