エクセル VBA 条件を複数指定したいのですが。。

Dim buf As Single
With sh1
For i = 6 To 200 'Sheet1の6行目から200行目まで'
If InStr(Range("F" & i), "空室,") > 0 Then
buf = buf + Range("K" & i)
End If

Next i

Range("J200") = "使用量"
Range("J201") = "空室等"
Range("J202") = "合計"
Range("K202").Formula = "=SUM(K3:K197)" '合計
Range("K201") = buf '空室等
Range("K200") = Range("K202") - Range("K201") '控除
For i = 1 To 3
Range("K" & i).NumberFormatLocal = "0.0"

Next i

三点、質問させていただきます。

1、空室　という文字が含まれる行の数値を合計させるために上記のようにしています。そこに、”共用部”や”管理外”など、条件の単語を追加したいのですが、
”空室,共用部,管理外”　ではダメなんですね、、
どのように記述すれば、複数の単語を対象に出来ますか？
それとも、行をコピーしておのおの指定するべきでしょうか？

2、6行目から200行目　ではなく、「6行目から下方向、データがある行まで」
というような、指定は出来ますでしょうか？？

3、マクロ実行後、セルの数値を手で打ち変えた際に、合計の値も連動して変わるようにするには、bufを使わないでやればいいのでしょうか？

'Range("AK243").Formula = "=SUM(AL3:AL239)" '空室合計計算
'Range("AK244").Formula = "=SUM(AK3:AK239)" '合計計算
'Range("AK242") = Range("AK244") - Range("AK243") '差引合計計算

上記の行を使うと、後でセルの数値を変更した場合連動して合計値なども変わってくれるのですが・・

添削をお願いできませんでしょうか。。
宜しくお願いいたします。

No.1 ベストアンサー 回答者： tatsu99 回答日時： 2017/07/06 09:29

1.以下のように変えてください。

If InStr(Range("F" & i), "空室,") > 0 Or InStr(Range("F" & i), "共用部,") > 0 Or InStr(Range("F" & i), "管理外,") > 0 Then

２．Sheet1の最大行をもとめておき、そこまで行うようにします。
Dim maxrow as Long
maxrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row '・・・・①
として
For i = 6 To maxrow
・・・・
next i
とします。
尚、①は、最大行を持つ列がAの場合です。もし、他の列が最大行を持っているならその列を指定して下さい。

３．
>マクロ実行後、セルの数値を手で打ち変えた際に、合計の値も連動して変わるようにするには、bufを使わないでやればいいのでしょうか？
>上記の行を使うと、後でセルの数値を変更した場合連動して合計値なども変わってくれるのですが・・
質問の意味がよくわかりませんが、上記の行を使ってうまく行くならそれを使用すればよいと思います。

この回答へのお礼

回答いただきましてありがとうございます。
早速、試してみたのですが、計算されませんでした・・
If InStr(Range("F" & i), "空室,") > 0 Or InStr(Range("F" & i), "共用部,") > 0 Then
とりあえず、条件二つにしてみてもだめでした。

最大行を求めておくわけですね、ありがとうございます。
どうも、(xlUp）の使い方が苦手でして、明示していただきありがとうございます。
後ほど、試して見ます！

お礼日時：2017/07/06 12:10

No.2 回答者： fujillin 回答日時： 2017/07/06 10:18

こんにちは

いまいちよくわからないところがありますが・・・

◇まずは、ご質問の内容に関して。
１、InStr関数の意味は、　『文字列の中に検索文字列があれば、その開始文字位置を返す』というものです。
この結果を『>0』で判定しているのは、検索文字があるか/ないかを判定していることになります。
さて、カウントなさりたいのが『「空室」,「共用部」,「管理外」のどれかがあれば』という意味であるなら、それぞれのチェックの論理和（or）を求めればよろしいでしょう。
例えば
　str = Range("F" & i).Value
If InStr(Str, "空室") Or InStr(Str, "共用部") Or InStr(Str, "管理外") Then
みたいな感じ。
（ちゃんと書くなら、InStr(～～) > 0 ですけれど）

2、ある列の値の入っている最終行を求めるには、最終セルから「end + ↑」入力と同様の処理で求めます。（良く用いられる方法です）
F列を例にするなら、
　Cells(Rows.Count, 6).End(xlUp).Row
で求めることができます。（6列＝F列です）

３、変数bufは、マクロ処理上の一時的に値を保存するための変数ですから、ご質問とは関係ないと思われます。
＞セルの数値を手で打ち変えた際に、合計の値も連動して変わるようにする
は、言い換えると、『値を変えたら、マクロの処理が動作する』という意味になります。
これを実現するためには、シートイベントの
　Worksheet_Change()
を利用すれば可能ですし、別の方法としては、結果を求めたいセルにユーザ定義関数として設定しておくといった方法も考えられます。


◇個人的な意見として
３でご質問のように、セルの値を変更したら集計結果が連動して変わる方が（一般的に）便利だと思います。
それを実現するごく普通の方法は、マクロではなくエクセルの関数式をセルに設定しておく方法です。（上述のようにマクロでも可能ですが）

内容の全貌がわからないので何とも言えませんが、例えば、ご質問のような処理だけであれば、COUNTIF()やCOUNTIFS()関数を利用した式を設定しておくことで、マクロを使用せずとも済むことのように思えます。
そうしておけば、わざわざ『値を変えたら自動的に反映』などと考えずとも、自動的に反映されますよね？