VBA エラー回避を教えてください。ループ内のMatchで値がないと止まってしまいます。

ループ内のMatchで値がないと止まってしまいます。
On Error Resume Nextで回避すると次のデータに前のデータが入力されてしまいます。



Dim TATE2 '「データ」シートの縦
Dim MaxRow

Dim 氏名
Dim 科目
Dim MaxCol
Dim myCol As Variant


MaxRow = Worksheets(シート2).Range("A6").End(xlDown).Row

MaxCol = Worksheets(シート2).Range("E3").End(xlToRight).Column

For 縦 = 6 To MaxRow '「データ」シートの縦
氏名 = Worksheets(シート2).Cells(縦, 4).Value

myRow = WorksheetFunction.Match(氏名, Worksheets(シート1).Range("D1:D200"), 0)

For 横 = 5 To 200

If Worksheets(シート2).Cells(5, 横).Value = "" Then

Else

科目 = Worksheets(シート2).Cells(5, 横).Value

myCol = WorksheetFunction.Match(科目, Worksheets(シート1).Range("E2:IZ2"), 0)

Worksheets(シート2).Cells(縦, 横).Value = Worksheets(シート1).Cells(myRow, myCol + 4).Value

End If

Next 横

Next 縦

No.7 回答者： WindFaller 回答日時： 2018/12/13 01:42

こんばんは。

On Error Resume Next を用いた方法は、Microsoft としては正統派の使い方です。

なまじ、ワークシート関数を使うと、その使いこなしは、あまりちゃんと出ていないので、いろんな回答が生まれてしまうのですが、その元凶は、マイクロソフト側がExcel 2000 VBAが仕様を変えたからなのです。

myRow = WorksheetFunction.Match(氏名, Worksheets(シート1).Range("D1:D200"), 0)
ここでは、そのままでは、残念ながら使えませんね。WorksheetFunction.Matchでは、VBA全体にエラーが走るので、On Error Resume Next を使うなら、

myRow ="" 　'←Variant 型に限る
myRow = WorksheetFunction.Match(氏名, Worksheets(シート1).Range("D1:D200"), 0)

If IsNumeric(myRow) then
・
・
・
myCol =""　'←Variant 型に限る
myCol = WorksheetFunction.Match(科目, Worksheets(シート1).Range("E2:IZ2"), 0)
If IsNumeric(myCol) Then

として、エラー値の時飛び越えたものをクリアしてあげないといけませんね。

余談ですが、
WorksheetFunction.Match とすることで、VBAにはワークシートの関数が組み込まれますが、エラーは、VBA全体に掛かってきてしまいます。
On Error Resume Next は、そのエラーを飛び越えて次のステップに行くようになりますが、その時の戻り値は、更新されていません。

昔から、VBAを使ってきた人は、Excel 97 スタイルで、Application.Match　と使います。そうすると、ワークシートで扱うのと同様に、エラー値と数値が戻り値で得られますから、IsNumeric(戻り値)だけで済むようになります。スピードもかなり速いはずです。この際、戻り値の変数は、必ず、Variant 型にするのがコツです。

この回答へのお礼

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

お礼日時：2019/02/05 18:06

No.6 回答者： tom04 回答日時： 2018/12/12 20:57

No.5です。

行もMATCH関数で検索していましたね。
前回の　IF～　を念のために

If IsNumeric(myRow) And IsNumeric(myCol) Then

にしてみてください。m(_ _)m

この回答へのお礼

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

お礼日時：2019/02/05 18:05

No.5 回答者： tom04 回答日時： 2018/12/12 20:51

こんばんは！

＞ループ内のMatchで値がないと止まってしまいます。

MATCH関数で返ってくるのは数値です。
対象がない場合はエラーになりますので、エラーでない場合のみ次へ進むようにしてみてはどうでしょうか？
未検証ですが・・・

＞Worksheets(シート2).Cells(縦, 横).Value = Worksheets(シート1).Cells(myRow, myCol + 4).Value
の1行を

If IsNumeric(myCol) Then
Worksheets(シート2).Cells(縦, 横).Value = Worksheets(シート1).Cells(myRow, myCol + 4).Value
End If

といった感じで・・・m(_ _)m

No.4 回答者： nngsss 回答日時： 2018/12/12 19:54

No.3訂正です

×　:MYMATCHERROR
○　MYMATCHERROR:

この回答へのお礼

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

お礼日時：2019/02/05 18:06

No.3 回答者： nngsss 回答日時： 2018/12/12 19:52

ちょっと不格好ですが別関数を作成してみてはどうでしょうか

呼び出し側
myRow = myMatch(氏名,"D1:D200")
あるいは
myCol = myMatch(科目,"E2:IZ2")

別関数
Function myMatch(ByVal word As String, ByVal area As String) As Long
On Error GoTo MYMATCHERROR
myMatch = WorksheetFunction.Match(word, Worksheets(シート1).Range(area), 0)
Exit Function

:MYMATCHERROR
myMatch = 0
Exit Function

End Function


で、以下の部分はif文で囲みます。
Worksheets(シート2).Cells(縦, 横).Value = Worksheets(シート1).Cells(myRow, myCol + 4).Value

If myRow = 0 Or myCol = 0 Then
Else
Worksheets(シート2).Cells(縦, 横).Value = Worksheets(シート1).Cells(myRow, myCol + 4).Value
End If

この回答へのお礼

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

お礼日時：2019/02/05 18:06

No.2 回答者： nngsss 回答日時： 2018/12/12 15:50

Matchで値が無いときにどういう処理を想定してるんでしょうか？

仮にMatchがうまく動いても、その先の
Worksheets(シート2).Cells(縦, 横).Value = Worksheets(シート1).Cells(myRow, myCol + 4).Value
で、わけがわからない状態になります。

値を探したい場合は
素直にVlookupやHlookupが使えないか検討してみてください

この回答へのお礼

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

お礼日時：2019/02/05 18:06

No.1 回答者： fujillin 回答日時： 2018/12/12 15:47

こんにちは

＞ループ内のMatchで値がないと止まってしまいます。
ワークシート関数のMatchはそういう仕様になっています。

＞On Error Resume Nextで回避すると次のデータに前のデータが入力されてしまいます。
ん？？

それって、On Error Resume Nextって1行を入れただけってことでしょうか？
であれば、「前のデータが入力されてしまいます」は十分に起こり得る結果です。
通常、エラー処理を行うような場合は、エラーが発生する可能性のある処理の直後（あるいはエラー時の分岐）で、『ラーの有無やその内容を調べて、それに応じて処理を行う』ようにするのが一般的です。

これに該当する処理がないのであれば、見かけ上は、「エラーが発生してもエラー表示はせず、そのままの状態から処理を継続する」ということになります。
「質問者様が思っていることを忖度して、うまく処理してくれる」ことを期待していらっしゃるならそのようなことはまったくありません。
というか、ご自分でそのようなコードをきちんと記述しておく必要があります。
エラー発生の有無を調べるには、Errオブジェクトをチェックするようにしてください。
https://docs.microsoft.com/en-us/office/vba/lang …


もしも、「エラー処理なんてよくわからん」というのであれば、ワークシート関数のMatchを利用する代わりに、VBAの場合はRange.Findメソッドを使用すればほぼ同様の機能ながらエラーが発生することはなくなります。
https://docs.microsoft.com/en-us/office/vba/api/ …

とは言え、該当するものが見つからない場合には戻り値がNothingとなりますので、結果をチェックし、それに応じた処理を行うということに関しては変わりはないでしょう。

この回答へのお礼

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

お礼日時：2019/02/05 18:06