オートフィルタを操作するマクロについて

質問

いつもお世話になっております。
現在オートフィルタを操作するマクロを作成中ですが、うまく動作してくれず、悩んでおります。
「検索画面」シートのＡ1から文字で検索条件を入力し、その答えを「元データ」のシートでオートフィルタを使って導き出すことをしています。
私が作ったマクロは以下の通りです。
Sub フィルタオプション設定()

Dim LastRow As Long, LastColumn As Long
Dim myData As Range
Dim myCriteria As Range

Set myCriteria = Worksheets("検索画面").Range("A1").CurrentRegion

Sheets("元データ").Select
Selection.AutoFilter Field:=26, Criteria1:=myCriteria, Operator:=xlOr
End Sub

これですと、「～を含む」という答えがでないのと、複数設定しても最後に入力した文字しか検索をかけないのです。

結局のところ、
（1）複数に加え、
（2）「～を含む」という条件で、
（3）別シートにあるデータをオートフィルタによって操作する
マクロをご教示ください！

よろしくお願いいたします。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

>Ａ2の空白まで検出しちゃいました。

こちらで試してみましたが、たぶん、検索値に空白文字が入っているのではないかと思っていますが、いかがでしょうか？それとも、データの範囲に空白文字が入っているとなると、結構ややこしいような気がします。

検索条件側は、
myCriteria1 = Trim(.Range("A1").Value)
myCriteria2 = Trim(.Range("A2").Value)

こんな風にすると、空白文字が除去できますが、空白文字自体の除去は、最初に作っていたけれども、ややこしくなるので途中で、そこまでは必要はないとしてアップロードしなかったのです。要するに、xlOr と xlAnd の使い分けですね。

ただ、次のコードがありますから、それは、後回しにして、次を試してください。

>３つ以上の条件で検出したいのですが、できますようでしょうか？

こんな内容になります。

条件を増やしたいなら、以下の5の部分を増やしてください。
　　arData(5) As String　
(5 + 1) が、検索条件の数になります。システム的には、確か、Excel 2003 までですと、30個程度だったと思います。

なお、フィルタオプションですから、ドロップダウン・ボタンは出てきません。
クライテリアの場所を、"AZ1:AZ2" を使っていますが、もうデータの範囲のMyData に隣り合わせにならなければ、どこでもよいです。

この種のものは非常に簡単なのですが、少しテクニックが必要なのと、セルのそれぞれの場所の特定が、慣れないとややこしいものです。

---------------------------------------------------

Sub フィルタオプション設定2()
　　Dim myData As Range
　　Dim arData(5) As String　'検索の入れ物は6個
　　Dim i As Integer
　　Dim j As Integer
　　Dim v As Variant
　　Dim a As String, f As String

　　Dim KenSakuRetu As Variant

　　KenSakuRetu = 26　'検索の対象列

　　Set myData = Worksheets("元データ").Range("A1").CurrentRegion

　　With Worksheets("検索画面")
　　　For Each v In .Range("A1").CurrentRegion.Columns(1).Cells
　　　　 arData(i) = Trim(v.Value)
　　　　 i = i + 1
　　　Next v
　　End With
　　'相対参照にするのがコツ
　　KenSakuRetu = myData.Cells(2, KenSakuRetu - 1).Address(0, 0)
　　For j = 0 To UBound(arData())
　　　If arData(j) <> "" Then
　　　　a = a & "CountIf(" & KenSakuRetu & ",""*" & arData(j) & "*""),"
　　　End If
　　Next j
　　f = "= OR(" & Mid$(a, 1, Len(a) - 1) & ")"
　 With myData
　　 .Range("AZ1:AZ2").ClearContents
　　 .Range("AZ1:AZ2").Cells(2).FormulaLocal = f
　　 .Range("A1").CurrentRegion.AdvancedFilter _
　　　　　　　 Action:=xlFilterInPlace, _
　　　　　　　 CriteriaRange:=.Range("AZ1:AZ2"), _
　　　　　　　 Unique:=False
　 End With
　 Set myData = Nothing
End Sub

お礼

すごい。。。。
こんなの自力では到底無理です。
一文一文をみて、確認しながらやってみます！
ご教示、誠にありがとうございました！

回答No.1

Wendy02

こんにちは。

>Set myCriteria = Worksheets("検索画面").Range("A1").CurrentRegion

このようにして、Criteria の領域を取っている場合は、フィルタオプションがよいですが、ただ、Criteria 自体の検索式が書くのが分からないと、出来ませんね。

今回は、オートフィルタでやってみました。この場合は、InputBox でもよさそうです。

Sub オートフィルタオプション設定()

　　'Dim LastRow As Long, LastColumn As Long
　　'Dim myData As Range
　　Dim myCriteria1 As String
　　Dim myCriteria2 As String
　　With Worksheets("検索画面")
　　　　myCriteria1 = .Range("A1").Value
　　　　myCriteria2 = .Range("A2").Value
　　End With
　　If myCriteria1 = "" And myCriteria2 = "" Then
　　　　MsgBox "検索値が入っていません", 48
　　　　Exit Sub
　　End If

　　'含むの設定
　　If myCriteria1 <> "" And Not myCriteria1 Like "~**~*" Then
　　　　myCriteria1 = "*" & myCriteria1 & "*"
　　End If

　　If myCriteria2 <> "" And Not myCriteria2 Like "~**~*" Then
　　　　myCriteria2 = "*" & myCriteria2 & "*"
　　End If

　　With Worksheets("元データ")
　　　　.Range("A1").CurrentRegion.AutoFilter 26, myCriteria1, xlOr, myCriteria2
　　End With
End Sub

補足

Wendy02様、ありがとうございます！
含むの設定ができました！
ただＡ1にしか条件をいれない場合、Ａ2の空白まで検出しちゃいました。
また、複数条件は２つまでですか？
３つ以上の条件で検出したいのですが、できますようでしょうか？
重ねてご教示頂ければ幸いです。