箸休め_8
前回(箸休め_7)に引き続き今回も「文字列操作.xlsm」を使って、検索に使える文字列操作について説明します。
今回の準備は「文字列操作.xlsm」を開いて、2枚目のシート名を「検索」として、下図のように作ってください。
表示窓の作り方については、図を参照してください。
ここまでで表示窓の動きを見ながら、これからやろうとすることや、そのアルゴリズムをイメージしてみてください。
今回使用する構文は、InStr(A, B) で「文字Bは文字列Aの何文字目か?」の意味です。
それでは、アルゴリズム(Excelさんへの依頼書)を考えて行きましょう。
まず、8行目E列に任意の検索文字、例えば「あ」を入れたとすると、前回作ったシート「差込」の名簿の「かな」(C列)に「あ」が含まれるとき、選択候補者として、当シートのN列に出力することで「表示窓」に表示されるはずです。
次に、表示窓から対象者一人を抽出すると、対象者に対して必要な処理を行うことができます。
ここでの必要な処理は前回同様、予約確認の文章を編集することとします。
アルゴリズムができたところで、コーディングしてみましょう。
C列を3行目から21行目まで繰り返し見るので、とりあえず
For n=3 To 21
Next n
とします。
For To ~ Next の中でやることは、シート「差込」のn行目C列の値に8行目E列の値が含まれているときは、その値をN列に出力することなので
If InStr(Sheets("差込").Cells(n, "C"), Cells(8, "E")) > 0 Then
P = P + 1
Cells(P + 1, "N") = Sheets("差込").Cells(n, "C")
End If
となります。
InStrは検索文字の位置を返すもので、検索文字が文字列の中に含まれていなければ0になるので、0より大きいときは含まれていることになります。
Pで対象件数をカウントアップして、1件目を2行目N列に出力するのでセルの行はP+1となります。
また、選択候補者が想定の5件を超えることもあるので、その時のために
If Cells(7, "N") <> "" Then
Cells(10, "I") = "OverFlow"
MsgBox " 選択肢が5件を超えています。"
End If
を付け加えておきましょう。
ここまでを整理すると
Sub SRC()
Range("N:N") = "": Cells(10, "I") = ""
For n = 3 To 21
If InStr(Sheets("差込").Cells(n, "C"), Cells(8, "E")) > 0 Then
P = P + 1
Cells(P + 1, "N") = Sheets("差込").Cells(n, "C")
End If
Next n
If Cells(7, "N") <> "" Then
Cells(10, "I") = "OverFlow"
MsgBox " 選択肢が5件を超えています。" & Chr(13) _
& " 検索キーを変えて絞り込んでください。" _
, 64, "箸休め_8 nJun"
End If
End Sub
となります。
繰り返し使用するために、冒頭でN列と、10行目I列の値を消去しています。
次に、対象者抽出後の処理は、前回同様シート「差込」の2行目H列の値に対象者の氏名、日付、時刻を入れて編集することです。
まず対象者を特定するためには、表示窓から選択された番号がCells(1, "N")にセットされていて、実際の値が番号+1行目N列にあるのでCells(7, "I")にその値を入れます。
Cells(7, "I") = Cells(Cells(1, "N") + 1, "N")
となります。
シート「差込」C列からCells(7, "I")の値と一致するものを取り出して、文字列編集したものを13行目D列に書き出すようにします。
Sub PIC()
Cells(7, "I") = Cells(Cells(1, "N") + 1, "N")
For n = 2 To 21
If Sheets("差込").Cells(n, "C") = Cells(7, "I") Then
ST = Replace(Sheets("差込").Cells(2, "H"), "≪氏名≫", Sheets("差込").Cells(n, "B"))
ST = Replace(ST, "≪日付≫", Format(Sheets("差込").Cells(n, "D"), "ggge年m月d日"))
ST = Replace(ST, "≪時刻≫", Format(Sheets("差込").Cells(n, "E"), "h時m分"))
Cells(13, "D") = StrConv(ST, vbWide)
Exit For
End If
Next n
End Sub
以上で完成です。
表示窓とSub PIC()を関連付けると、実行ボタンをクリックする感覚で選択肢をクリックすると、抽出と同時にProcedureが実行されます。
検索Key(8行目E列の値)を変えて、N列の表示を見ながら動きを確認してください。
この動きが確認できたら、N列を非表示にすると画面がスッキリなると思います。
最後にInStrについて補足します。
たとえば、mj=InStr("2024年2月18日は日曜日です", "日")
とするとmjの値は10となります。
「一番左にある”日”は10文字目です」の意味です。
「一番右にある・・・」とするためには
mj=InStrRev("2024年2月18日は日曜日です","日")としますが、
mjの値は14となります。
これは、「一番右側にある”日”は左から14文字目です」となることに注意が必要です。
また、真ん中の「日」の位置を取得するためには
mj=InStr("2024年2月18日は日曜日です", "日曜")とすると
mjの値が12となります。
今回は以上です。
最後までご覧いただき、ありがとうございました。
この記事が気に入ったらサポートをしてみませんか?