[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『検索値がないときのエラー回避方法』（わかあゆ）
皆様のお力添えをいただきながら、少しずつ、イメージを形にしているところです。 今回は、次のマクロコードで、検索値が入力されていないときの、対処方法について ご助言、ご指導いただきたいのです。
Sub Test40()
Dim myR As Long, myR2 As Long, myR3 As Long, i As Long
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
myR = Application.WorksheetFunction.Match(Sheets("週案作成作業").Range("$A$130"), Sheets("学級基本設定").Range("$A$13:$A$378"), 0) '転記先先頭行表中位置取得
myR2 = myR + 12 '表中位置を行番号に変換
myR3 = 130 '転記元開始行番号を変数に
For i = 1 To 7
Sheets("学級基本設定").Range("P" & myR2 + (i - 1) & ":AS" & myR2 + (i - 1)).Value = Sheets("週案作成作業").Range("C" & myR3 + (i - 1) & ":AF" & myR3 + (i - 1)).Value
'順に行単位で転記
Next i
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
End Sub
上記マクロで、週案作成作業シートの$A$130セルが未入力のとき、エラーとなることが考えられます。 そこで、そのような場合、処理を抜けて、MsgBoxで"作業対象週を選択していますか。選択してから 再度開始ボタンを押してください"とメッセージを出したいのですが、お教えいただけませんか？
Application.WorksheetFunction.Match もしくは、 WorksheetFunction.Match これ使うなら、on error resume next でかわしてください。 対象がないと実行エラーになる。
Application.Match こうすると、対象がない場合エラー値が返るから、iserror(変数)などで、判定できます。
Application.Match で、検索すると使用例が出ると思います。 BJ
BJさん、ありがとうございます。
>myR = Application.WorksheetFunction.Match(Sheets("週案作成作業").Range("$A$130"), Sheets("学級基本設定").Range("$A$13:$A$378"), 0)
の「myR = Application.WorksheetFunction.Match(」の部分を
「myR = Application.Match(」とすることは分かりました。
しかし、
「If IsError(myR) Then
MsgBox "作業対象週を選択していますか。選択してから再度開始ボタンを押してください"」を含め
他にどのような記述をどこに入れればよいのか、検討がつきません。
こうではないか、ああではないかと試行を繰り返していますが、クリアできません。
>Application.Match で、検索すると使用例が出ると思います。
本当に沢山ありました。しかし、なかなか、理解できず、思ったように動作するマクロに仕上げる
ことができません。
厚かましいこととは思いますが、今一度、ご教授願えませんか。
(わかあゆ)
myRに数式の結果を入れたらすぐに
IF IsError かどうかを確認して THEN メッセージを表示 エラーで無い場合(else) 現在行っている処理 表中位置を行番号に変換〜順に行単位で転記 を行い END IF
すれば良いのではないでしょうか？
再計算は、セルに値を書き込むときにおきると思うので ELSE の時の処理の中で行っても良いのではないかと思います。
(HANA)
>週案作成作業シートの$A$130セルが未入力のとき、エラーとなることが考えられます。 私なら、原因が想定できるエラーのときは、その原因に対しての分岐を考えます。 If [Range Object].Value <> "" Then '処理 Else '[Range Object].Value = "" のときの処理 End If さらに、Match関数を使う前に、Countif関数であらかじめ対象範囲に検査値が 存在することを確認するような仕様にします。 (みやほりん)(-_∂)b
HANAさん、みやほりんさん、ありがとうございます。 HANAさんのコメントを参考に、以下のようなコードに訂正してみました。
Sub Test41()
Dim myR As Long, myR2 As Long, myR3 As Long, i As Long
myR = Application.Match(Sheets("週案作成作業").Range("$A$130"), Sheets("学級基本設定").Range("$A$13:$A$378"), 0) '転記先先頭行表中位置取得
If IsError(myR) Then
MsgBox "作業対象週を選択していますか。選択してから再度開始ボタンを押してください"
Else
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
myR2 = myR + 12 '表中位置を行番号に変換
myR3 = 130 '転記元開始行番号を変数に
For i = 1 To 7
Sheets("学級基本設定").Range("P" & myR2 + (i - 1) & ":AS" & myR2 + (i - 1)).Value = Sheets("週案作成作業").Range("C" & myR3 + (i - 1) & ":AF" & myR3 + (i - 1)).Value
'順に行単位で転記
Next i
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
End If
End Sub
セル"$A$130"が未入力のとき、
"myR = Application.Match(Sheets("週案作成作業").Range("$A$130"), Sheets("学級基本設定").Range("$A$13:$A$378"), 0) '転記先先頭行表中位置取得"
の部分でエラー、「型が一致しません」という表示が出てしまいます。
未入力でなければ、正常終了します。
また、みやほりんさんのを参考に、以下のようなコードに訂正してみました。 やはり、セル"$A$130"が未入力のとき、同じ箇所で、同じ表示が出てエラーとなります。
Sub Test42()
Dim myR As Long, myR2 As Long, myR3 As Long, i As Long
If Sheets("週案作成作業").Range("$A$130").Value <>"" Then
MsgBox "作業対象週を選択していますか。選択してから再度開始ボタンを押してください"
Else
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
myR = Application.Match(Sheets("週案作成作業").Range("$A$130"), Sheets("学級基本設定").Range("$A$13:$A$378"), 0) '転記先先頭行表中位置取得
myR2 = myR + 12 '表中位置を行番号に変換
myR3 = 130 '転記元開始行番号を変数に
For i = 1 To 7
Sheets("学級基本設定").Range("P" & myR2 + (i - 1) & ":AS" & myR2 + (i - 1)).Value = Sheets("週案作成作業").Range("C" & myR3 + (i - 1) & ":AF" & myR3 + (i - 1)).Value
'順に行単位で転記
Next i
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
End If
End Sub
同じ箇所で、同じエラーメッセージですので、共通の問題だと考えることができますが、 力不足で原因が分かりません。（思考が袋小路に入ってしまったのでしょうか） よきアドバイスをお願いします。 なお、本問題以外でも、コード内に不備等がありましたら、ご指導ください。 (わかあゆ)
えっと。。。
＞If Sheets("週案作成作業").Range("$A$130").Value <>"" Then
これだと、A130に入力が有るときにメッセージが表示されませんか？
入力の確認をして 無かった時に（=""） → 「未入力！！」 入力があったら、どこにあるか Match関数で取得しますが その値が無かった時に（IsError） → 「入力値不正！！」 その値が有ったときに → 転記処理を実行
ですよね？
(HANA)
HANAさん、やっぱり、袋小路に入っていました。
指摘されれば、そうだよ、そうだよな、
>入力の確認をして 無かった時に（=""） → 「未入力！！」
反対ではないか、動かないのは当然だ。
早速、
>If Sheets("週案作成作業").Range("$A$130").Value <>"" Then を
If Sheets("週案作成作業").Range("$A$130").Value = "" Then にしましたら、正常に終了しました。
当該セルが未入力のときには、ボックスが表示されました。
>入力があったら、どこにあるか Match関数で取得しますが
> その値が無かった時に（IsError） → 「入力値不正！！」
> その値が有ったときに → 転記処理を実行
ここのところは、これから始まる会議終了後に挑戦してみます。
結果は後ほどお知らせします。
問題が生じたときには、再度、質問いたしますので、よろしくお願いします。
(わかあゆ)
仮にA130が入力されていても、検索範囲にデータがなければエラーでストップしまっせ。
Dim myR As Long を Variantにしときませう。
そうすればA130が未入力を含めてヒットしない時に、止まらないでIsErrorへ分岐します。
（弥太郎）
弥太郎さん、ご助言ありがとうございました。
変数myRの型をVariantにし、If Sheets("週案作成作業").Range("$A$130").Value = "" Then 〜
関連部分を削除し、かつIf IsError(myR) Then 〜部分を挿入して、下記のようなコードに訂正
したところ、未入力時でも、検索範囲内に合致する値がないときでも、適切に作動しました。
Sub Test43()
Dim myR As Variant, myR2 As Long, myR3 As Long, i As Long
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
myR = Application.Match(Sheets("週案作成作業").Range("$A$130"), Sheets("学級基本設定").Range("$A$13:$A$378"), 0) '転記先先頭行表中位置取得
If IsError(myR) Then
MsgBox "「未入力、または入力値不正！！作業週選択状況を確認してください」"
Else
myR2 = myR + 12 '表中位置を行番号に変換
myR3 = 130 '転記元開始行番号を変数に
For i = 1 To 7
Sheets("学級基本設定").Range("P" & myR2 + (i - 1) & ":AS" & myR2 + (i - 1)).Value = Sheets("週案作成作業").Range("C" & myR3 + (i - 1) & ":AF" & myR3 + (i - 1)).Value
'順に行単位で転記
Next i
Application.Calculation = xlCalculationManual '再計算モードを手動に設定
End If
End Sub
ちなみに、Test42では検索範囲内に合致する値がないときには、型が一致しないとして エラーで止まってしまいました。 マクロは難しいですが、おもしろい！ (わかあゆ)
あ・・・あれ？ 「再計算モードを手動に設定」を二回するのですか？
てっきり、最初が「手動」で最後が「自動」だと思って 読み違えてたみたいです。。。
どうして同じ設定を二回やりますか？
それと、実はこっちが気になっていたのですが
For i = 1 To 7
'順に行単位で転記
Next i
ループさせる必要はなさそうに思いますが。。。
例えば、 myR2 が 1 だったら P1:AS7 に値を入れるのですよね？ つまり "P" & myR2 &":AS" & myR2 + 6 の範囲。
(HANA)
やってしまっていました。当然、自動に戻しますので、 Application.Calculation = xlCalculationAutomatic '再計算モードを自動に設定 ですね。 う〜ん、言われてみればそうですね。なぜ、１行ずつ？ 固まりで転記するでよいですね。７行３０列の固まりで一気に転記です。 わざわざ難しくしていますね。 HANAさん、重ね重ねありがとうございました！ (わかあゆ)
＞当然、自動に戻しますので、 でしたら、最初の手動にする位置を下げておいた方が良いと思いますよ。
Test41 のコードでは、IF の中に入っていたと思いますが。
それから、シートが二つ出てきますが どちらのシート名も二回ずつ使われていますので 一度変数に入れておいて それを使うことにした方が 良いかもしれません。
シート名が変更になったときに 一箇所の変更で済むようになるので。
(HANA)
HANAさん、感謝いたします。手動にする位置をIf以下に下げておきます。
後々の変更も視野に入れたコードづくりをということでしょうか。
お言葉、大切にします。
Dim S1 As Worksheet, S2 As Worksheet
Set S1 = Worksheets("週案作成作業")
Set S2 = Worksheets("学級基本設定")
という具合でしょうか。
(わかあゆ)
＞後々の変更も視野に入れたコードづくり と言うか、「一寸テストしたい」とか言う時に シート名を変更して回るのが面倒なので。。。
いや、後々の変更も視野に入れて！！（汗）
変数名は「S1」だとセル番地っぽいので 別の名前にして於いた方が良いかもですね。
さらに考えるなら、もっとシートの本質が 分かるような変数名に出来ると良いかもしれません。 （なんて、人のことは言えないのですが。）
ちなみに私は、Set は苦手なので Dim sn1 As String sn1 = "週案作成作業" とか、途中で変更する事が無いなら Const sn1 As String = "週案作成作業" とかすると思います。
使う時には
Sheets(sn1).Range("$A$130")
って感じで使う事に成りますが。
(HANA)
変数の型を柔軟に考えるとちょっと違った世界が見えるものですね。 シートを変数で扱う時は、変数の型は Worksheetと決め込んでいましたから。 大変参考になりました。 (わかあゆ)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.