関数の中で参照するセル範囲(変動する)の記述方法

質問

セルJ5に下記の数式を入力するマクロを組みたいのですが、記述方法が分かりません。
　　=SUMIF(セル範囲(1),B5,セル範囲(2))
　・セル範囲(1)にはB5：C列の最終データまで
　・セル範囲(2)にはC5：C列の最終データまで
自分なりに
　　ActiveCell.Formula = "=SUMIF(Range(Cells(5, 2), Cells(Range("b5").End(xlDown).Row, 3)),b5,Range(Cells(5, 2), Cells(Range("C5").End(xlDown).Row, 3)))"
と書いてみたのですがダメでした。
どうかご教授お願い致します。

回答No.2

KURUMITO · ベストアンサー

回答No１です。
直接にセルに式を入れるのでしたら単に次の式でもよいですね。

=SUMIF(B5:B100,B5,C5:C100)))

マクロの場合でもB列の最終行を求めて式にする必要もありませんので例えば次のようにしてもよいですね。

ActiveCell.Formula = "=SUMIF(B5:B100,B5,C5:C100)))"

お礼

ありがとうございます！
これで解決してしまいました。
最初のご回答もとても勉強になりました。

回答No.4

keithin

マクロについてのご質問ですから，マクロについて回答します。
今回はマクロで「書かせたい数式文字列」を作文して，formulaにセットします。

もっともあなたがご相談に書いた
＞=SUMIF(セル範囲(1),B5,セル範囲(2))
　・セル範囲(1)にはB5：C列の最終データまで　←これはただの誤記だとしても
　・セル範囲(2)にはC5：C列の最終データまで
では，Ｂ５セルがダブって範囲に含まれてしまうので「正しい数式」では無いと判断でき，適切な回答は出来ません。

たとえばいまＢ列の最下行がＢ１０で，Ｃ列の最下行が（Ｂ列と変わるのも変なお話に見えますが）仮にＣ２０だとすると，「実際に書かせたい数式」は
=SUM(B5:B10,B5,C5:C20)
となります。ここでＢ１０の「１０」の文字と，Ｃ２０の「２０」の文字だけが，マクロを実行した時点で書き換えなきゃならない部分です。
sub macro1()
　dim LastRowB as long
　dim LastRowC as long
　lastrowB = range("B65536").end(xlup).row
　lastrowC = range("C65536").end(xlup).row

　range("J5").formula = "=SUM(B5:B" & lastrowB & ",B5,C5:C" & lastrowC & ")"
end sub

といった具合です。
formulaに入れる代わりに
msgbox "=SUM(B5:B" & lastrowB & ",B5,C5:C" & lastrowC & ")"
とでも書いて，「欲しい数式の文字列」が間違い無く作成できているか確認してから，実地に行ってみると良いでしょう。

お礼

とても丁寧で分かり易くご説明頂きありがとうございます。
教えて頂いた構文は今後もかなり役に立ちそうです。
大変勉強になりました。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

マクロというより、数式ですが
Sub Macro2()
　 　ActiveCell.FormulaR1C1 = _
　 　"=SUMIF(R5C2:INDEX(R5C2:R10000C2,COUNT(R5C3:R10000C3))," & _
　 　"R5C2,R5C3:INDEX(C3,COUNT(R5C3:R10000C3)))"
End Sub

Sub Macro3()
　 　Dim 最終行 As Long
　 　最終行 = Range("b5").End(xlDown).Row
　 　ActiveCell.FormulaR1C1 = _
　 　"=SUMIF(R5C2:R" & 最終行 & "C2,R5C2,R5C3:R" & 最終行 & "C3)"
End Sub

>　=SUMIF(セル範囲(1),B5,セル範囲(2))
は相対参照が含まれているので、どのセルに入力するかで変化します。
上記マクロは絶対参照として回避しましたが、質問時にどのセルに入力するかも書くようにしてください。

お礼

ありがとうございます。
とても参考になりました。

回答No.1

KURUMITO

Formulaでセルに式を入力する場合にはマクロを使わなくても計算できる形の式でなければいけませんね。Cells.Rangeなど式に入れることはできません。
どうしてマクロを使って式を入れようとしているのかが分かりません。
マクロを使った計算ならばセルに式を入力するのではなく直接に計算すればよいのでたとえば次のように記述すればよいでしょう。

n = Range("B5").End(xlDown).Row

Range("J5") = WorksheetFunction.SumIf(Range(Cells(5, "B"), Cells(n, "B")), Range("B5"), Range(Cells(5, "C"), Cells(n, "C")))

ただ単にマクロを使わない式でしたらJ５セルには例えば次の式を入力すればよいでしょう。

=SUMIF(B5:INDIRECT("B"&5+COUNTIF(B5:B100,"<>")),B5,C5:INDIRECT("C"&5+COUNTIF(C5:C100,"<>")))

この場合にはB５セルからB１００までの間にデータがある場合です。B1000としてもよいでしょう。