VBA でワークシート関数(SUMIF)と変数を組み合わせる

Question

先ほどのSubの連続するプログラムの続きなのですが質問内容が変るので別スレッドを投稿させていただきます。

VBAにてワークシート関数のSUMIFを使用したいのですが、同時に変数も取り入れたいと思います。

ゴールは

H555にAcompanyの金額の合計

H556にBcompanyの金額の合計

H557にCcompanyの金額の合計

の値を入力することです。が、最終行が毎回変るので変数を使用したいと思います。（画像は小さいサイズにまとめるため途中の行を非表示にしております）

Sub Test2()

Dim RowNumbers2 As Long '最終行を変数にすると宣言

RowNumbers2 = Worksheets("Posted").Range("A2").End(xlDown).Row  '最終行をRowNumbers2変数に格納(この例の場合は554)

RowNumbers2 = RowNumbers2 + 1 'Acompanyの合計を入れる行数をRowNumbers2に格納(この例の場合は555)

'以下のコードは正しく動作する(SUMIFの式に変数を使用せず手動で行数を入力したから）

'Worksheets("Posted").Range("H" & RowNumbers2).Formula = "=SUMIF(A2:A554,""Acompany"",H2:H554)"

'しかし以下のコードはどうしてもエラーになってしまう。

Worksheets("Posted").Range("H" & RowNumbers2).Formula = "=SUMIF(A2:A&"RowNumbers2-1",""Acompany"" ",H2:H&"RowNumbers2-1" & ")"

RowNumbers2-1で変数を555から554に戻そうとしております。直接数字を入れたら動作するんだから後はその部分に変数を入れ込めばよいだけ･･･と思うのですが簡単に行きません・・・。

恐れ入りますが、もしよろしかったらアドバイスをいただけると幸いです。

よろしくお願いいたします。

Answer 1

=SUMIF(A2:A554,"Acompany",H2:H554)

を文字列と変数の部分を分離して考えて下さい。

文字列：

=SUMIF(A2:A

変数にしたい部分：

554

文字列：

,"Acompany",H2:H

変数にしたい部分：

554

文字列：

)

で構成されています。

これを文字列の部分だけを""で囲み、それぞれを&で繋ぎます。

文字列の中に""がある部分は（"Acompany"の部分）"を2つ続けて記載します。

"=SUMIF(A2:A" & ",""Acompany"",H2:H" & ")"

&の後に、変数の部分をつなげて、

"=SUMIF(A2:A" & RowNumbers2 - 1 & ",""Acompany"",H2:H" & RowNumbers2 - 1 & ")"

と記述します。

Answer 2

nombo様：

ありがとうございました。目からうろこがおちるごとくわかりました。。。唖然としております。

先に

"=SUMIF(A2:A554,""Acompany"",H2:H554)"

という数字の直接入力で希望通りの動作ができたと書きました。　その際Acompanyを””　　　””で囲って

おりましたが、理屈はまったく理解しておりませんでした。　ただ、そうしないと動かない・・・という

理由だけで順番に試して上記のようにコードを書きました。

しかし、nombo様の教えてくださった通り、このようなコードを書く時には本当はしっかり決まった

ルールがあるのですね。　わたしは今までこのルールをまったく理解していなかったので

" と ' と , と ) と &　をわけのわからないまま順番に動くまでいろいろ組み合わせていました。何時間も何時間もかけて・・・。

しかし今回nombo様が初級者にもわかるようにご教授して下さったので、どこで何をどのように分け、

その後どのような処理をし、どのように繋げ、どこに変数を入れるか理解できました。

今後はあてづっぽうに" と ' と , と ) と &　をできるまで延々と組み合わせる必要はなくなりました。

（いえ、多分失敗はいっぱいするでしょうが、少なくともコードの書き方に方向性が見出せました。）

うーん。。。本当におもしろい・・・と思う反面、わたしはなぜこんなに勉強しているのに勉強していることから

こういうことがわからないんだろう・・・というか、勉強していることからこのことを応用で引き出せないんだろう・・・

となさけなくなります。

しかし、今日習ったことは明日に引き継がれると思うので引き続きがんばります。

心からお礼を申し上げます。　ありがとうございました。

ところで画像が挿入できるようになってびっくりしています。マイクロソフト様がこのコニュにティの

希望に耳を傾けていた・・・ということにびっくりしています。　いや･･･悪口ではなく、素直に

感動しております。