For~next 内にある WorksheetFunction.MATCH のエラー回避手段について

Question

前提・実現したいこと

EXCELシートの右側に、ランダムに並んだ文字列があります。
文字列の構造は[文字(値)]という構造です。
上記の文字列から、各行ごとに指定の文字列が含まれるセルを検索し、そのセルの値部分のみをどんどん書き出していきたいです。
指定の文字列が含まれるセルが存在しない場合は、エラー値または空白などが入る想定ですが「ない」ことさえわかれば何でもOkです…!

発生している問題・エラーメッセージ

WorksheetFunction.MATCHのエラーをうまく回避できず、進めません。。。
エラーではない値は、7周目（i=8）で初めて発生します。そこまで全てエラー値です。

該当のソースコード

Dim 検索値 As String, i As Long, c As Long, MatchC As Variant, MatchR As Range

c = ActiveCell.Column
検索値 = Cells(1, c).Value
For i = 2 To 200000

 On Error Resume Next
 MatchC = WorksheetFunction.MATCH(検索値, Range(Cells(i, c), Cells(i, "LHL")), 0) + c
 If MatchC = 0 Then
 Set Cells(i, MatchC) = ""
 GoTo nexti
 
 ElseIf Cells(i, 1).Value = "" Then Exit Sub
 
 Else:
  Set MatchR = Cells(i, MatchC)
  Cells(i, c) = Mid(MatchR, InStr(MatchR, "(") + 1, InStr(MatchR, ")") - InStr(MatchR, "(") - 1)
End If
nexti: MatchC = ""
Next i
End Sub

試したこと

[On error Goto nexti]だと、2周めで「worksheetfunctionのmatchファンクションを取得できません」と出てしまいます。

[IsError(MatchC) then Goto nexti]だと、1周もしません。。
[Application.MATCH]も同上です。

[On Error Resume Next]で回避して、[If MatchC = 0 Then]で判定する手段もためしましたが、
[On Error Resume Next]の前に[MatchC=0]をいれるとずっと0のままで、
入れないと7周目以降も、7周目時点で代入された値を引き継いでしまいます。

補足情報（FW/ツールのバージョンなど）

EXCEL2016です。

Answer 1

「たぶんこうしたかったんじゃないか」で書いてみました。

  Dim 検索値 As String
  Dim i As Long
  Dim c As Long
  Dim MatchC As Long
  Dim MatchV As String

  c = ActiveCell.Column
  検索値 = Cells(1, c).Value

  For i = 2 To 200000
    If Cells(i, 1).Value = "" Then Exit Sub                           ' Exit判定は先頭
    MatchC = 0                                                        ' 初期化
 
On Error Resume Next
    MatchC = WorksheetFunction.Match(検索値, Range(Cells(i, c), Cells(i, "LHL")), 0)
On Error GoTo 0                                                        ' On Errorは必要最低限の範囲
 
    If (MatchC = 0) Then                                               ' Matchの成否判定はMatchの結果のみで行なう
      Cells(i, c) = ""                                                 ' 一致しなければクリア
      GoTo NEXTI                                                       ' 以降スキップ
    End If
    '
    ' ELSE。 上記のif条件成立時はgotoでスキップするので「Else」ステートメントは不要
    '
    MatchC = MatchC + c - 1                                            ' オフセットを補正
    MatchV = Cells(i, MatchC).Value                                    ' 値を取り出す。Rangeである必要なし
    Dim sx As Long: sx = InStr(MatchV, "(") + 1                        ' 同じ値を複数回使うなら変数に入れる(趣味の問題だが)
    Cells(i, c) = Mid(MatchV, sx, InStr(sx, MatchV, ")") - sx)         ' 括弧内取り出し

NEXTI:

  Next i

ちなみに、コード中のコメントにも書きましたがOn Errorを使うと
エラーメッセージが表示されませんのでエラーの箇所がわかりにくく
なります。On Errorの範囲は必要最小限にしたほうがよいです。
なお、エラーの出ていたのはMatchではなく以下の部分ではないかと。

If MatchC = 0 Then
Set Cells(i, MatchC) = ""

↑ (1)文字列の設定に"set"は不要
↑ (2)Match=0の処理なのでCells(i,0) を参照(indexエラー)。

Comments

[mai._123]

ご回答いただきありがとうございます！「On Error Resume Next」と「On Error GoTo ～」をセットで使う知識がなかったことが今回ネックだったようです。式の説明を入れていただいたおかげで、細かい部分をじぶんのやりたい形に調整して書き直すこともでき、それにより理解も深まりました。ありがとうございました！

Answer 2

こういう、繰り返しの作業は、
変数を使いまわしますので、
変数の初期化というのを意識して書いて下さい。
（今回の場合は、検索する前に変数の値を0に戻しておく）

Option Explicit

Sub test()
    Dim rngKey As Range
    Dim rngFind As Range
    Dim r As Range
    Dim sKey As String
    Dim ix As Long

    Set rngKey = ActiveCell
    sKey = rngKey.Value
    With ActiveSheet
        Set rngFind = .Range(rngKey, .Cells(.Rows.Count, "LHL").End(xlUp))
    End With

    For Each r In rngFind.Rows
        ix = 0
        On Error Resume Next
        ix = WorksheetFunction.Match(sKey, rngFind.Cells, 0)
        On Error GoTo 0

        With r.Cells(1)
            If ix > 0 Then
                .Value = Val(Split(r.Cells(ix).Value, "(")(1))
            Else
                .ClearContents
            End If
        End With
    Next
End Sub

で見つかれば0より大きい答えが返ってきますし、
見つからなければ、エラーとなり、その行の実行は無視して、
次の行に処理が移りますので、
変数の中身は変わりません。
それによって条件分岐をしたらよいと思います。

あと、
Cells(i, 1).Value = ""
というのは、セルを空白にしようとしているのでしょうか？
「””」は長さ0の文字列ですので、
「””」をいれたら、空白には「見える」が、空白になっていません。
エクセル的には、セルを空白にするのは、クリアの処理なので、
「値のクリア」の命令を実行したらいいと思います。
ｖBA的には、空白　＝　Empty
ですので、ValueプロパティにEmptyをセットするのもありだとは思います。

それから、
今回の件の場合、
操作の対象はセル範囲なので、
セル範囲を変数に代入するようにした方が、
見た目がすっきりするかなと思います。
処理速度も上がるかも？
まぁ、この辺は個人の好みもありますので、
こういう意見もあるということを知っていただければいいかなと思います。

もう一つ、
何回くらいループすることになるかわかりませんが、
WorksheetFunctionで何回も繰り返すと、
処理がかなり重いかなと思います。
ワークシート上に数式を入れた方が処理は速くなると思います。
（それなりのテクニックは必要）
その辺は検討の余地があるかなと思いました。

あ、
「値」というのは、「数値」の意味ですよね?
文字列も値の一種なので、ちょっと分かり難い表現です。

Comments

[sazi]

With ActiveSheet.UsedRange　は宣言だけになっていませんか？

[tatsu99]

提示されたマクロは動作確認されていないように思われます。
Option Explicit
を付加して、動作確認されたマクロを提示していただけるとありがたいのですが・・・

[mattuwan]

ご指摘ありがとうございます。
一応、誤字・脱字を修正し、コンパイルで確認してました。
が、動作確認をする義務はないと考えます。
（テストデータのイメージが分からないので）
今回のテーマは、変数の初期化でしょうから、
その辺の処理の流れをイメージで掴んでいただければ幸いです。

[mai._123]

ご回答いただきありがとうございます。変数の初期化や軽く動作させるためのテクニックについてご教授いただきとても勉強になりました！ありがとうございます。
今回は、EXCEL音痴のメンバーに向けて作成しておりましたため「ココ押せばおけ」状態にする必要があり、ワークシート上に数式や関数を使ってのシンプルな形式を摂ることができないといった事情がございました。説明が不足しており申し訳ございません。。。
このたびは、誠にありがとうございました！

Answer 3

すみません。セルの配置がよくわからないのですが、添付図のようになっているということでしょうか。
検索文字列は、選択された列の1行目の文字列（添付図のC1のセル）
検索結果は、C2～C４へ設定する。（添付図の黄色のセル）
このように読み取れますが、あってますか。
私が提示したような内容で、あなたの環境のセルの配置を画像で提示していただけませんでしょうか。

Comments

[mai._123]

ご回答いただきありがとうございます。画像が情報保護の観点からかなりの加工を加えなくてはならなかったため、開示することができませんでした、、、。
やりたかったこととしては、上記画像においてご説明させていただくと、D列以降のセルに左詰めで「東京(45)」「東京(３)」「大阪(57)」等の文字列が入力されており、各行内において文字列に挟まれる形の空白セルは発生しません。この中から、「東京」の後ろの数値（つまり 45 や 3 など）を、C列に書き出したかったといった意図でした。
丁寧にご返信いただきほんとうにありがとうございました！