■
全て作業列を使うパターンです。
顧客名簿に全角でも半角でも処理できるようになっています。
スクロールの問題修正。
会員番号の前方一致。
オートフィルターが常に表示されるように修正。
名前・フリガナの検索方法を変更。
2度目の検索で表示されない不具合を修正。
作業列を定数に変更。
顧客名簿がアクティブにならない不具合を修正。
検索文字を選択するように変更。
Sub MacroClientSearch() '顧客名簿のパスを環境に合わせてください Const myPath As String = "C:\Documents and Settings\hogehoge\デスクトップ\顧客名簿.xls" '顧客名簿のブック名 Const wbName As String = "顧客名簿.xls" '顧客名簿のワークシート名 Const wsName As String = "顧客名簿" '作業列の指定 Const workColumn As Integer = 100 Dim srcWS As Worksheet '--- 検索元シート Dim dstWS As Worksheet '--- 検索先シート Dim srcTitle As String '--- 検索タイトル Dim dstTitleRange As Range '--- 検索先タイトル Dim searchWord As String '--- 検索ワード Dim count As Long '--- 検索ヒット数 Dim ar As Long Dim sr As Range '--- 作業用変数 Dim sWord As Variant Dim swords As Variant Dim lastRow As Long Dim i As Long Dim rg As Range Set sr = Selection '--- 複数セル選択した場合のエラー処理 If Selection.count <> 1 Then MsgBox ("複数のセルが選択されています。") Exit Sub End If Set srcWS = ActiveSheet ar = ActiveWindow.ScrollRow srcTitle = srcWS.Cells(1, Selection.Column).Value searchWord = Selection.Value '--- タイトル列がない場合の処理 If Len(srcTitle) = 0 Then MsgBox searchWord & "の列名は存在しません。検索場所を確認してください。" Exit Sub End If On Error GoTo Err_Mes If bookCheck(myPath) Then Set dstWS = Workbooks(wbName).Worksheets(wsName) Else Set dstWS = Workbooks.Open(myPath).Worksheets(wsName) End If On Error GoTo 0 '--- 検索先にタイトル列がない場合の処理 Set dstTitleRange = dstWS.Rows(1).Find(what:=srcTitle, lookat:=xlWhole) If dstTitleRange Is Nothing Then MsgBox searchWord & "の列名は存在しません。検索場所を確認してください。" Exit Sub End If '--- 表示されていないフィルターを解除 On Error Resume Next dstWS.ShowAllData On Error GoTo 0 '--- 検索語の準備処理 dstWS.Columns(workColumn).Clear dstWS.Cells(1, workColumn).Value = "○" lastRow = dstWS.Cells(Rows.count, dstTitleRange.Column).End(xlUp).Row Select Case dstTitleRange.Value Case "会員番号" '半角にして比較 For i = 2 To lastRow If StrConv(dstWS.Cells(i, dstTitleRange.Column).Value, vbNarrow) Like _ StrConv(searchWord, vbNarrow) & "*" Then dstWS.Cells(i, workColumn).Value = "○" End If Next Case "メール", "携帯メール" '半角にして比較 For i = 2 To lastRow If StrConv(dstWS.Cells(i, dstTitleRange.Column).Value, vbNarrow) = _ StrConv(searchWord, vbNarrow) Then dstWS.Cells(i, workColumn).Value = "○" End If Next Case "旧会員番号" '半角にして比較 swords = Split(searchWord, "/") For Each sWord In swords For i = 2 To lastRow If InStr("/" & StrConv(dstWS.Cells(i, dstTitleRange.Column).Value, vbNarrow) & "/", _ "/" & StrConv(sWord, vbNarrow) & "/") > 0 Then dstWS.Cells(i, workColumn).Value = "○" End If Next Next Case "名前", "フリガナ" '全角にして空白を削除して比較 For i = 2 To lastRow If Replace(StrConv(dstWS.Cells(i, dstTitleRange.Column).Value, vbWide), " ", "") Like _ "*" & Replace(StrConv(searchWord, vbWide), " ", "") & "*" Then dstWS.Cells(i, workColumn).Value = "○" End If Next Case "電話番号" '半角にしてハイフンを削除して比較 For i = 2 To lastRow swords = Split(dstWS.Cells(i, dstTitleRange.Column).Value, "/") For Each sWord In swords If haifun(CStr(sWord)) = haifun(searchWord) Then dstWS.Cells(i, workColumn).Value = "○" End If Next Next Case Else MsgBox searchWord & "の列名は存在しないか。その列では検索できません。" _ & vbNewLine & "検索場所を確認してください。" Exit Sub End Select '--- 検索処理 dstWS.Activate If dstWS.AutoFilterMode Then dstWS.Range("A1").AutoFilter End If dstWS.Cells.AutoFilter dstWS.Range("A1").AutoFilter Field:=workColumn, Criteria1:="○" lastRow = dstWS.Cells(Rows.count, workColumn).End(xlUp).Row '--- 検索結果がない場合 If lastRow = 1 Then MsgBox "検索キーワード「" & searchWord & "」に該当する行は見つかりませんでした。" _ & "検索条件を変えてみてください。" dstWS.Range("A1").AutoFilter Field:=workColumn srcWS.Activate Exit Sub End If '--- 検索結果があった場合 dstTitleRange.Offset(1).Resize(lastRow - 1, 1).SpecialCells(xlVisible).Select For Each rg In Selection count = count + 1 Next If srcWS.Name = wsName Then sr.Select Else dstTitleRange.Select End If If MsgBox("検索キーワード「" & searchWord & "」に該当する " & count & " 行を表示しました。" _ & vbNewLine & "最初のページに戻りますか?" & vbNewLine & _ "[はい]→最初のページに戻って検索をやり直す/検索を終了する。" & vbNewLine & _ "[いいえ]→このページの検索結果を確認する。", _ vbYesNo, "最初のページに戻りますか?") = vbYes Then dstWS.Range("A1").AutoFilter Field:=workColumn srcWS.Activate ActiveWindow.ScrollRow = ar End If Exit Sub Err_Mes: Select Case Err.Number Case 1004 MsgBox "顧客管理をオープンできません。パスを確認してください。" Case 9 MsgBox "顧客管理の正しいブック名とシート名を指定してください。" Case Else MsgBox "顧客管理をオープンすることができませんでした。" End Select End Sub 'ブックが開いているかをチェック Function bookCheck(myPath As String) As Boolean Dim f As Boolean Dim myBook As Workbook For Each myBook In Workbooks If myBook.Path & "\" & myBook.Name = myPath Then f = True Exit For End If Next bookCheck = f End Function '半角にして−を削除 Function haifun(str As String) As String str = StrConv(str, vbNarrow) str = Replace(str, "-", "") str = Replace(str, "―", "") str = Replace(str, "ー", "") str = Replace(str, "−", "") haifun = str End Function
回答3の変更です。
スクロールの問題を修正。
会員番号の前方一致。
オートフィルターを常に表示。
名前・フリガナの検索方法を変更。
2度目の検索で表示されない不具合を修正。
作業列を定数に変更。
顧客名簿がアクティブにならない不具合を修正。
検索文字を選択するように変更。
Sub MacroClientSearch() '顧客名簿のパスを環境に合わせてください Const myPath As String = "C:\Documents and Settings\hogehoge\デスクトップ\顧客名簿.xls" '顧客名簿のブック名 Const wbName As String = "顧客名簿.xls" '顧客名簿のワークシート名 Const wsName As String = "顧客名簿" '作業列の指定 Const workColumn As Integer = 100 Dim srcWS As Worksheet '--- 検索元シート Dim dstWS As Worksheet '--- 検索先シート Dim srcTitle As String '--- 検索タイトル Dim dstTitleRange As Range '--- 検索先タイトル Dim dstTitleRangeBK As Range '--- 検索先タイトル保存用 Dim searchWord As String '--- 検索ワード Dim searchWordBK As String '--- 検索ワード保存用 Dim count As Long '--- 検索ヒット数 Dim ar As Long '--- スクロール位置 Dim sr As Range '--- 作業用変数 Dim sWord As Variant Dim swords As Variant Dim lastRow As Long Dim i As Long Dim rg As Range Set sr = Selection '--- 複数セル選択した場合のエラー処理 If Selection.count <> 1 Then MsgBox ("複数のセルが選択されています。") Exit Sub End If Set srcWS = ActiveSheet ar = ActiveWindow.ScrollRow srcTitle = srcWS.Cells(1, Selection.Column).Value searchWord = Selection.Value '--- タイトル列がない場合の処理 If Len(srcTitle) = 0 Then MsgBox searchWord & "の列名は存在しません。検索場所を確認してください。" Exit Sub End If On Error GoTo Err_Mes If bookCheck(myPath) Then Set dstWS = Workbooks(wbName).Worksheets(wsName) Else Set dstWS = Workbooks.Open(myPath).Worksheets(wsName) End If On Error GoTo 0 '--- 検索先にタイトル列がない場合の処理 Set dstTitleRange = dstWS.Rows(1).Find(what:=srcTitle, lookat:=xlWhole) If dstTitleRange Is Nothing Then MsgBox searchWord & "の列名は存在しません。検索場所を確認してください。" Exit Sub End If '--- 表示されていないフィルターを解除 On Error Resume Next dstWS.ShowAllData On Error GoTo 0 '--- 検索語の準備処理 Select Case dstTitleRange.Value Case "会員番号" searchWord = StrConv(searchWord, vbNarrow) & "*" Case "旧会員番号" searchWord = StrConv(searchWord, vbNarrow) searchWordBK = searchWord Set dstTitleRangeBK = dstTitleRange dstWS.Columns(workColumn).Clear dstWS.Cells(1, workColumn).Value = "○" swords = Split(searchWord, "/") lastRow = dstWS.Cells(Rows.count, dstTitleRange.Column).End(xlUp).Row For Each sWord In swords For i = 2 To lastRow If InStr("/" & dstWS.Cells(i, dstTitleRange.Column) & "/", "/" & Trim(CStr(sWord)) & "/") > 0 Then dstWS.Cells(i, workColumn).Value = "○" End If Next Next searchWord = "○" Set dstTitleRange = dstWS.Cells(1, workColumn) Case "名前", "フリガナ" searchWord = Replace(searchWord, " ", "*") '--- 半角スペースの置換 searchWord = "*" & Replace(searchWord, " ", "*") & "*" '--- 全角スペースの置換 Case "電話番号" searchWord = haifun(searchWord) searchWordBK = searchWord Set dstTitleRangeBK = dstTitleRange dstWS.Columns(workColumn).Clear dstWS.Cells(1, workColumn).Value = "○" lastRow = dstWS.Cells(Rows.count, dstTitleRange.Column).End(xlUp).Row For i = 2 To lastRow swords = Split(dstWS.Cells(i, dstTitleRange.Column).Value, "/") For Each sWord In swords If haifun(CStr(sWord)) = searchWord Then dstWS.Cells(i, workColumn).Value = "○" End If Next Next searchWord = "○" Set dstTitleRange = dstWS.Cells(1, workColumn) Case "メール", "携帯メール" searchWord = StrConv(searchWord, vbNarrow) Case Else MsgBox searchWord & "の列名は存在しないか。その列では検索できません。" _ & vbNewLine & "検索場所を確認してください。" Exit Sub End Select '--- 検索処理 dstWS.Activate If dstWS.AutoFilterMode Then dstWS.Range("A1").AutoFilter End If dstWS.Cells.AutoFilter dstWS.Range("A1").AutoFilter Field:=dstTitleRange.Column, Criteria1:=searchWord lastRow = dstWS.Cells(Rows.count, dstTitleRange.Column).End(xlUp).Row If dstTitleRange.Column = workColumn Then searchWord = searchWordBK End If '--- 検索結果がない場合 If lastRow = 1 Then MsgBox "検索キーワード「" & searchWord & "」に該当する行は見つかりませんでした。" _ & "検索条件を変えてみてください。" dstWS.Range("A1").AutoFilter Field:=dstTitleRange.Column srcWS.Activate Exit Sub End If '--- 検索結果があった場合 If dstTitleRange.Column = workColumn Then dstTitleRangeBK.Offset(1).Resize(lastRow - 1, 1).SpecialCells(xlVisible).Select Else dstTitleRange.Offset(1).Resize(lastRow - 1, 1).SpecialCells(xlVisible).Select End If For Each rg In Selection count = count + 1 Next If srcWS.Name = wsName Then sr.Select Else dstTitleRange.Select End If If MsgBox("検索キーワード「" & searchWord & "」に該当する " & count & " 行を表示しました。" _ & vbNewLine & "最初のページに戻りますか?" & vbNewLine & _ "[はい]→最初のページに戻って検索をやり直す/検索を終了する。" & vbNewLine & _ "[いいえ]→このページの検索結果を確認する。", _ vbYesNo, "最初のページに戻りますか?") = vbYes Then dstWS.Range("A1").AutoFilter Field:=dstTitleRange.Column srcWS.Activate ActiveWindow.ScrollRow = ar End If Exit Sub Err_Mes: Select Case Err.Number Case 1004 MsgBox "顧客管理をオープンできません。パスを確認してください。" Case 9 MsgBox "顧客管理の正しいブック名とシート名を指定してください。" Case Else MsgBox "顧客管理をオープンすることができませんでした。" End Select End Sub 'ブックが開いているかをチェック Function bookCheck(myPath As String) As Boolean Dim f As Boolean Dim myBook As Workbook For Each myBook In Workbooks If myBook.Path & "\" & myBook.Name = myPath Then f = True Exit For End If Next bookCheck = f End Function '半角にして−を削除 Function haifun(str As String) As String str = StrConv(str, vbNarrow) str = Replace(str, "-", "") str = Replace(str, "―", "") str = Replace(str, "ー", "") str = Replace(str, "−", "") haifun = str End Function