VBAで文字と数値のセルを計算したい

質問

EXCEL2000でＶＢＡ作成中です。

　以下のコードで計算式を入れています。
セルの値が0のときは、セルの値を表示しないようにしています。
ところが印刷すると0が表示されてしまいます。

Range("F18").Formula = "=if(+G18>0,""朝"","""")"
Range("h18").Formula = "=if(+Ｉ18>0,""昼"","""")"
Range("J18").Formula = "=if(+Ｋ18>0,""夕"","""")"

Range("G18").Formula = "=IF(COUNTIF(Q18:AB19, ""
朝"")=0,0,COUNTIF(Q18:AB19, ""朝""))"
Range("I18").Formula = "=IF(COUNTIF(Q18:AB19, ""
昼"")=0,0,COUNTIF(Q18:AB19, ""昼""))"
Range("K18").Formula = "=IF(COUNTIF(Q18:AB19, ""
夕"")=0,0,COUNTIF(Q18:AB19, ""夕""))"
Range("L18").Formula = "=-(+G18*300+I18*350+K18*
400)"

そこで
Range("G18").Formula = "=IF(COUNTIF(Q18:AB19, ""
朝"")=0,"""",COUNTIF(Q18:AB19, ""朝""))"
とすると、L18にエラーが出て計算してくれません。
ゼロを非表示にしてしかも計算させるようにするには
どうしたらよろしいか。

回答No.5

taocat · ベストアンサー

またまたこんばんは。

＞式＝-(+G18*300+I18*350+K18*400)　とします。

これは”式”を　-(+G18*300+I18*350+K18*400)　に置き換えて読んで下さいということです。
"="　は要りません。

＞Range("L18").Formula = "=If(ISERROR(=-(+G18*300+I18*350+K18*400)),"""",=-(+G18*300+I18*350+K18*400))"

この式の、=-(+G18*300+I18*350+K18*400)の前の"="は不要です。

Range("L18").Formula = "=If(ISERROR(-(+G18*300+I18*350+K18*400)),"""",-(+G18*300+I18*350+K18*400))"

以上です。

お礼

今再度試したらできました。ありがとうございました。

補足

今ためしたのですがセルに公式が入るだけで結果の値が表示されません。すいません。

回答No.4

KenKen_SP

こんにちは。KenKen_SP です。

【方法1】「ゼロ値」の表示オブションを使う

[ツール]-[オプション]-[表示]のウインドウオプションで[ゼロ値]
のチェックをはずすと IF 関数などで制御しなくとも、表示も印刷
もされませんが、ダメですか？

【方法2】セルの表示形式を使う

セル表示形式のユーザー定義は、以下のように「；」で区切って
それぞれの書式を定義できます。

　　正の数の場合 ; 負の数の場合 ; 0 の場合 ； 文字列の場合

つまり、

　　#,##0;-#,##0;;@

みたいに、「0の場合」の書式に何も指定しないと先に述べた方法と
同様の結果が得られます。

予め、セルにこのような書式を設定しておけば良いのですが、これも
マクロでやるなら、

　　With Range("L18")
　　　　.NumberFormatLocal = "#,##0;-#,##0;;@"
　　　　.Formula = "=-(+G18*300+I18*350+K18*400)"
　　End With

こんな感じです。

これらの手法をうまく使うと計算式を簡素化できるメリットがあります。

補足

今ためしたのですが＃ＶＡＬＵＥ！がでてしまいます。すいません。かっこいいコードなので使いたいですが。

回答No.3

e10go

>セルL18には、かならず「0」が印刷されてしまいます。
>これを印刷しないようにするのは不可能でしょうか？

マクロの、

Range("L18").Formula = "=-(IF(G18="""",0,G18)*300+IF(I18="""",0,I18)*350+IF(K18="""",0,K18)*400)"

上のコードを、

Range("L18").Formula = _
"=IF(AND(G18="""",I18="""",K18=""""),"""",-(IF(G18="""",0,G18)*300+IF(I18="""",0,I18)*350+IF(K18="""",0,K18)*400))"

に変えてください。これで、セルL18の「0」表示も印刷もされないです。

お礼

このコードをもっと研究します。ありがとうございました。

補足

今ためしたところ、完璧にできました。

回答No.2

e10go

「0」と表示されて困るセルに、セルの書式設定で、ユーザ定義の「#」を設定すれば、値が「0」でも画面上・印刷上では「0」が出ません。

書式設定を標準のまま、セルG18・I18・K18の計算結果が""でエラーを出さないようにするには、マクロの一部を下のように直せば良いです。

Range("G18").Formula = "=IF(COUNTIF(Q18:AB19, ""朝"")=0,"""",COUNTIF(Q18:AB19, ""朝""))"
Range("I18").Formula = "=IF(COUNTIF(Q18:AB19, ""昼"")=0,"""",COUNTIF(Q18:AB19, ""昼""))"
Range("K18").Formula = "=IF(COUNTIF(Q18:AB19, ""夕"")=0,"""",COUNTIF(Q18:AB19, ""夕""))"
Range("L18").Formula = "=-(IF(G18="""",0,G18)*300+IF(I18="""",0,I18)*350+IF(K18="""",0,K18)*400)"

なお、計算結果によっては、セルL18には、「0」が表示されます。

補足

今ためしたのですが
セルL18には、かならず「0」が印刷されてしまいます。
これを印刷しないようにするのは不可能でしょうか？

回答No.1

taocat

こんにちは。

今回のように、G18に長さ０の文字列、""　が入っていると
=G18*300　とかの計算は#VALUEエラーが出ますので
ISERRORとか使いエラーをトラップしなければなりません。

で、次のように書くことができます。
また、コードが長くなりますので

　式＝-(+G18*300+I18*350+K18*400)　とします。

Range("G18").Formula = "=If(ISERROR(式),"""",式)"

以上です。

補足

今試してみたのですがアプリまたはオブジェクトエラーになってしまいました。なんででしょうか？
Range("G18").Formula = "=IF(COUNTIF(Q18:AB19, ""朝"")=0,"""",COUNTIF(Q18:AB19, ""朝""))"
Range("I18").Formula = "=IF(COUNTIF(Q18:AB19, ""昼"")=0,"""",COUNTIF(Q18:AB19, ""昼""))"
Range("K18").Formula = "=IF(COUNTIF(Q18:AB19, ""夕"")=0,"""",COUNTIF(Q18:AB19, ""夕""))"
Range("L18").Formula = "=If(ISERROR(=-(+G18*300+I18*350+K18*400)),"""",=-(+G18*300+I18*350+K18*400))"