エクセルマクロのCells.Findについて

質問

• エクセルマクロのCells.Findを使用して、特定の文字列を含むセルを検索する方法について教えてください。
• Cells.FindのWhat:=の記入方法を工夫することで、スペルという文字列を検索する際にゴスペルという文字列を排除しながら検索することが可能です。
• また、他の方法でも特定の文字列を含むセルを検索することができます。

回答No.7

matsu_jun

tktkmanureさん、おはようございます。ANo.6のmatsu_junです。

FindSpecialというファンクションを作ってみました。
FindSpecialの書式は以下のとおりです

FindSpecial(TgtKey, TgtRng, ExcKey(), SplKey, AftRng)
　TgtKey : 検索したい文字列です。質問中の「スペル」に相当します。
　TgtRng : 検索する範囲を示します。質問中では「Cells」に相当します。
　ExcKey() : 検索から除外したい文字列です。質問中の「ゴスペル」に相当しますが、
　　　　　　例えば「ゴスペル」だけでなく、「スペルチェック」や「スペルリスト」も除外したい場合
　　　　　　　ExcKey(0) = "ゴスペル"
　　　　　　　ExcKey(1) = "スペルチェック"
　　　　　　　ExcKey(2) = "スペルリスト"
　　　　　　とします。配列は必ず(0)から記載してください。
　　　　　　除外したいリストにない、上の例の場合、「スペルミス」などは検索対象となります。
　SplKey : セルの中で、幾つかの単語が続けて記載されている時に、単語どうしを分割している
　　　　　　記号です。質問中の「、」に相当します。実際の用途では区切り文字がないという場合でも
　　　　　　適当な文字（「、」のままでよいです）を入れておいて下さい。
　AftRng : TgtRngで指定した範囲における、検索スタートの位置を示します。一つのセル(例えばRange("A1"))で示します。
　　　　　　検索範囲を"A1:B10"とした時、ここにRange("A5")と記載すれば、1行目から4行目およびA5は検索対象に含まず
　　　　　　B5とA6からB10までの範囲で検索を行います。(Find関数のafterと同じものです)
　　　　　　省略はできないので、初めて検索する際は、検索範囲の左上のセルを記載してください。

FindSpecial は、Rangeオブジェクトになります。

なので、
　Dim ResultCell As Range
　Set ResultCell = FindSpecial( 以下 引数 … )
といった形で御利用ください。

さて、実際のコードを以下に示します。

試しに、以下をSheet1のモジュールにコピーしてください。

'------------------------------ここからコピー------------------------------------
Private Function FindSpecial(TgtKey As String, TgtRng As Range, ExcKey() As String, SplKey As String, AftRng As Range) As Range
'TgtKey : 検索対象となる文字
'TgtRng : 検索範囲（レンジで指定）
'ExcKey : 検索から除外する文字（配列で指定）
'SplKey : 検索するセルに複数の単語が含まれている際、それらを区切るためのキャラクター（例通りなら"、"）
'AftRng : AftRngより先のセルから検索
　　Dim FirstFind As Range　　'最初の検索結果を保持（検索が一周したことを検知するため）
　　Set FindSpecial = TgtRng.Find(TgtKey, After:=AftRng)
　　If Not (FindSpecial Is Nothing) Then
　　　　Set FirstFind = FindSpecial
　　　　'とりあえず「TgtKey」を含むセルの検索が終了。以下、検索結果から除外されるかどうかの判断
　　　　If FindExeclude(FindSpecial.Value, ExcKey(), SplKey) Then Exit Function　　'除外する文字で検索されていなければ、このセルを返り値として終了
　　　　Do Until (FindSpecial Is Nothing)　　'とりあえず検索した結果が、実は除外する文字でも検索されていたならば、次のセルを検索
　　　　　　Set FindSpecial = TgtRng.FindNext(FindSpecial)
　　　　　　If FindSpecial.Address = FirstFind.Address Then Exit Do　　'検索結果が1周したら、結果なしとして終了
　　　　　　If FindExeclude(FindSpecial.Value, ExcKey(), SplKey) Then Exit Function　　'除外する文字で検索されていなければ、このセルを返り値として終了
　　　　Loop
　　End If
　　Set FindSpecial = Nothing　　'ルーチンが本行まで実行された場合は結果なしと判断されるので、返り値をNothingとして終了
End Function

Private Function FindExeclude(TgtStr As String, ExcKey() As String, SplKey As String) As Boolean
'FindSpecialの補助関数（ユーザーは実際に呼び出さない）
'TgtStr : とりあえず検索したセルの中身
'ExcKey : 検索から除外する文字（配列で指定）
'SplKey : 検索するセルに複数の単語が含まれている際、それらを区切るためのキャラクター（例通りなら"、"）
　　Dim SplStr() As String
　　Dim FindCnt As Integer
　　FindExeclude = False
　　'TgtStrをSplKeyで分割し、配列に格納
　　SplStr = Split(TgtStr, SplKey)
　　For i# = 0 To UBound(SplStr)
　　　　FindCnt = 0
　　　　For j# = 0 To UBound(ExcKey)
　　　　　　FindCnt = FindCnt + InStr(SplStr(i), ExcKey(j))　　'配列データが、除外データに相当するかどうかの検索（除外データが含まれていたら、FindCntは、1以上の値となる
　　　　Next j
　　　　If FindCnt = 0 Then　　'全ての除外データについて確認し、それらが含まれていなければTrueを返す
　　　　　　FindExeclude = True
　　　　　　Exit Function
　　　　End If
　　Next i
End Function
'------------------------------ここまでコピー------------------------------------

試験方法として、以下をお試しください。

ExcelのシートのA1セルに、検索したい文字として
スペル
と入力してください。
A2セルに、検索から除外したい文字として
ゴスペル、スペルチェック、スペルリスト
と入力してください。（読点もそのまま入力してください）

後は同じシート上に、適当にデータを記入してみてください。
(上記の通り「スペルミス」は除外文字として登録していないので検索対象です。
適当なセルに「スペルミス」と入力してください)

それができたら、シート上にボタン(CommandButton1)を配置します。
で、以下をコピーして、同じモジュールに貼り付けます。

'------------------------------ここからコピー------------------------------------
Private Sub CommandButton1_Click()
　　Dim JOGAI() As String
　　Dim ResultRange As Range

　　JOGAI = Split(Range("B2"), "、")　　'…(1)
　　FindSpecial(Range("B1"), Cells, JOGAI, "、", ActiveCell).Select
End Sub
'------------------------------ここまでコピー------------------------------------

シートに戻って、ボタンを押してみてください。検索できた場所にカーソルが移っていきます。
そのまま連打すれば、次の検索場所にカーソルが移ります。

今回の例では、検索から除外したい文字列を、「、」で区切って1つのセルに入れています。
これを1つずつ配列に格納するには、上のコードの(1)の部分 (Split関数) を利用します。
1セル内に複数の文字列を改行して入れている場合（セルに文字入力中 Alt+Enterで改行させていた場合）
上の(1)の行は

　　JOGAI = Split(Range("B2"), Chr(10))

と書き換えられます。
今回作成した FindSpecial関数内の引数 SplKey についても、もし区切りの文字が改行であった場合は
Chr(10)を利用します。

よろしければお試しください。

お礼

matsu_junさん
大変お手数をおかけしました。ありがとうございました。
大変有用なファンクションだと思いますし、マクロ作成における物事の考え方が大変参考になります。ただ、これまた私の質問の仕方や情報が悪いのですが、複数の単語が含まれているセル内の区切り文字が、私の手元にある表の場合、「、」だったり、「,」だったり、改行だったり、スペースだったり、数字だったり、1つの表の中で統一されておらず、しかも1つのセルの中でそれらが混ざっていたりしています。作っていただいたマクロの修正を考えたのですが、ちょっと厳しそうです。今のところは、mt2008さんのANo.5やANo.8のロジックがより汎用的だと思いますので、そちらを使わせていただこうかと思います。