『続き』 質問番号：701776EXCEL_VBA

質問

■質問番号701776の続きです。よろしくお願いいたします。

http://okwave.jp/qa/q7017764.html

■依頼内容
下記コードで アプリケーション定義またはオブジェクト定義のエラー(実行時エラー1004)を修正したい。

エクセルの関数で　=IF(ISERROR(A1/B1),"",A1/B1)　といった関数を作成すると、エラー値は非表示で、エラーでない場合のみ、計算しますが、
VBAで同様の式を利用しようとすると、エラーとなって実行できません。ご教授お願いいたします。

Sub macro1()
Dim w As Workbook
Dim mydate As Date, mydays As Long

Set w = Workbooks.Open(Filename:=ThisWorkbook.Path & "\実験データ.xls")

mydate = ThisWorkbook.Worksheets(1).Range("E1")
mydate = mydate - Day(mydate) + 1 '念のため。
mydays = Day(DateAdd("M", 1, mydate) - 1)
ThisWorkbook.Worksheets(1).Range("C5:I35").ClearContents

With ThisWorkbook.Worksheets(1).Range("C5").Resize(mydays, 7)
.Formula = "=IF(ISERROR(VLOOKUP($E$1-DAY($E$1)+$B5,[実験データ.xls]data!$B:$K,MATCH(C$4,[実験データ.xls]data!$B$1:$K$1,0))),"",VLOOKUP($E$1-DAY($E$1)+$B5,[実験データ.xls]data!$B:$K,MATCH(C$4,[実験データ.xls]data!$B$1:$K$1,0)))"

.Value = .Value
End With

w.Close savechanges:=False
End Sub

回答No.2

keithin · ベストアンサー

＞エクセルの関数で　=IF(ISERROR(A1/B1),"",A1/B1)　といった関数を作成すると、
＞エラー値は非表示で、エラーでない場合のみ、計算します

=A1/B1という数式がエラーになるのは，
１）エラーの具体的な内容が#DIV/0だった場合：
　それはＢ１がゼロであるのがエラーの直接の原因なので，数式は
　=IF(B1=0,"",A1/B1)
とすれば良いことが判ります。

２）エラーの具体的な内容が#VALUE!だった場合：
　それはA1かB1に「文字列」が記入されているのがエラーの直接の原因なので，数式は
　=IF(COUNT(A1:B1)=2,A1/B1,"")
などのようにすればよいことが判ります。

３）エラーの具体的な原因を追及するのがメンドクサイ場合：
　その場合は，まぁどんなエラーでも対応できる
　=IF(ISERROR(A1/B1),"",A1/B1)
としても勿論構いません。
でも数式がシンプルにA1/B1程度ならまだしも，もっともっと長くなってきた場合でも，「長い（重い）数式」をイチイチ計算してエラーにならないかまず確認してから，改めて本番の計算を同じだけやり直すのを「不細工だ」と思わなければ，です。

＞VBAで同様の式を利用しようとすると、エラーとなって実行できません。

新しいマクロの記録を開始します
所定のセルに
=IF(B1=0,"",A1/B1)
と数式を記入しEnterして実際に一度式を記入させてみます
マクロの記録を終了し，採取できたマクロを参考にして，「この式を記入させる」には一体どんなマクロにすれば良かったのか確認します。

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2011/9/20 ユーザー名 : keithin
'

'
　ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,"""",RC[-2]/RC[-1])"
　Range("C2").Select
End Sub

むむー。。R1C1形式のマクロが録れてしまいましたが，まぁ肝心の「""」の部分が「""""」と書けばよい事が判ったことで良しとしましょう。

実際のC5セルに（若しくはC5:I35セルに）
=IF(ISERROR(VLOOKUP($E$1-DAY($E$1)+$B5,[実験データ.xls]data!$B:$K,MATCH(C$4,[実験データ.xls]data!$B$1:$K$1,0))),"",VLOOKUP($E$1-DAY($E$1)+$B5,[実験データ.xls]data!$B:$K,MATCH(C$4,[実験データ.xls]data!$B$1:$K$1,0)))
あるいは
=IF(MEDIAN(MIN([実験データ.xls]data!$B:$B),$E$1,MAX([実験データ.xls]data!$B:$B))=$E$1,"",VLOOKUP($E$1-DAY($E$1)+$B5,[実験データ.xls]data!$B:$K,MATCH(C$4,[実験データ.xls]data!$B$1:$K$1,0)))
といった数式を記入する操作を新しいマクロの記録でマクロに録って，どんなマクロの書きぶりをしたら正しく動作するのか，確認しながら作成してみてください。

お礼

回答ありがとうございます。お礼が遅くなってすみません。

回答までの導き方まで、ご丁寧に教えていただきありがとうございました。

マクロを使うといった発想が全くなかったので、考え方の幅が広がりました。

keithin様のレベルには程遠いですが、少しでも精進させていただきます。

ありがとうございました。

回答No.1

imogasi

Sub test01()
Range("C1").Formula = "=IF(iserror(A1/B1),"""",A1/B1)"
End Sub
を実行してみて。エクセル関数の埋め込みという点では、まく行くのでは。
ＶＢＡでエクセルの式の埋め込みはやらないほうが良いと思うが個人的な趣味だが。
””のＶＢＡにおける表現の経験が質問者には無かったからだけのような感じがする。
ーー
また
依頼内容の上記の実例と、コードを貼り付けている実例（ＶＬＯＯＫＵＰ等）と、違うじゃない。こういう質問の仕方はやると読者が迷惑する。
コード実例の
Formula = "=IF(ISERROR(VLOOKUP($E$1-DAY($E$1)+$B5,[実験データ.xls]data・・が疑問点ならそれに絞ること。
ーー
それまでのコード（日付をいじくっている部分？）も論点がぼやけるので、省いたほうが良いと思う。
読者・回答者が速く判りやすい質問の仕方を考えてください。それが質問者の思考の訓練に役立つ。

お礼

全体のコードが見えたほうが良いと思って、そのまま全て載せてしまいました。

ＶＢＡの理解力をあげて、適切な質問ができるように精進いたします。ありがとうございました。