アドバンスフィルターについて

質問

住所録を管理できるシステムを作っています。
２番目のシートを検索＆結果表示のシート、３番目～１０番目のシートにはユーザーフォームを使ってデータを入力して蓄積していくシートとしています。
検索はアドバンスフィルターを使っています。
Dim Rng As Range
Dim cRng As Range
Dim sh As Variant
With Sheets(2).Select.Range("A13:L10000").ClearContents
For Each sh In Array(Sheets(3), Sheets(4), Sheets(5), Sheets(6), Sheets(7), Sheets(8), Sheets(9), Sheets(10))
Set cRng = .Range("A65536").End(xlUp).Offset(1)
If cRng.Row < 13 Then
Set cRng = .Range("A13")
End If
sh.Range("A2:L3002").AdvancedFilterAction:=xlFilterCopy, _
CriteriaRange:=.Range("A6:L7"), CopyToRange:=cRng, _
Unique:=False
cRng.Resize(, 17).Cleaｒ
Next sh
End With
End Sub
このマクロでは、ユーザーフォームを使って入力していったデータを検索することはできるのですが、他のエクセルシートからデータをコピー＆ペーストしたり、直接入力をすると検索できません。
何が原因なのでしょうか。教えてください。

回答No.7

Wendy02

>空白の検出について、うまく説明できないのですが、
>自分のイメージしているものは、検索値に何もない（空白）の場合
>検索するシートの入力値、空白すべてを表示させるのではなく、
>入力値のみを表示させたいと思っています

せっかく、お礼側に書いていただいきましたが、一体、どのようになるのか、ずっと考えても、さっぱり分かりません。具体性がないものに関して、こちらも回答のしようがありません。その都度、検索範囲を変えればよいと思います。

もともと、常識的は、検索値が、12項目もあるというのか不自然です。だから、その中で非該当の検索値があるというもの当然の話です。

ただ、#6で回答した以上に該当する現象と例を、私に自分で考えて探して直せ、とおっしゃっているわけだと思いますが、内容を明かさずには、もう限界があります。会社では、そういう方式が良くあると聞きますが、あいにく、私は、会社員の経験があっても、フリーに近い状態でやっきましたし、現在はフリーですから、こういう方式には不慣れです。私の書いたマクロについてはこちら側が出来る限りのことはしますが、それ以上に、手作業ではうまく行っているのではないかと思いますが、そういう部分は、具体的に公開していただかないと、私には無理です。

回答No.6

Wendy02

>うまく検索されましたが、空白も検出されてしまいました。
検索式の問題でしょうから、そこまでは正直な所分かりません。検索式の問題は、何を入れているのか、実際は分かっていないのですから、こちらで解答を出すことは不可能に近いです。そういう可能性をいくつも出さなくてはなりません。こちらのマクロの問題というなら、まだ何ともいいようもあるのでずか、マクロ自体の問題とも言えそうにもありません。

もともと「空白の検出」という意味が分からないのです。

可能性のいくつかを挙げてみます。

　sh.Range("A2:L3002")　'←この範囲が違っている？
　でしたら、
　　↓
With sh.Range("A2").CurrentRegion
　 Set myData = sh.Range("A2", .Cells(.Count))
End With　　
　　 myData.AdvancedFilter _
　　 ・
　　 ・
　　 ・

>CriteriaRange:=.Range("A6:L7") それとも、
つまり、AからLまではないということですか？

もしそうなら、

.Range("A6:L7")
　　　↓
.Range("A6", Cells(7,Columns.Count).End(xlToLeft))

それとも、検索式の入れる範囲に空白値が入っている。
ならば、

For Each c in .Range("A6", Cells(7,Columns.Count).End(xlToLeft))
　If Trim(c.Value) ="" Then
　　 c.ClearnContents
　End If
Next

なお、このご質問は、ページとしては、9ページまで来ていますので、補足に入れても、気がつくまでには時間が掛かることがあります。お礼側に一報を入れてくれればこちらの連絡が入りますが、そうでなければ、返事は遅くなることがあります。ただし、あえてお礼を要求するわけではありません。単に、ここの板の機能的な面を示しているだけです。

お礼

お礼側に入れると連絡がはいるのですね。知りませんでした。
本当にたびたび申し訳ありません。
空白の検出について、うまく説明できないのですが、
自分のイメージしているものは、検索値に何もない（空白）の場合
検索するシートの入力値、空白すべてを表示させるのではなく、
入力値のみを表示させたいと思っています。

回答No.5

Wendy02

返事遅くなってすみません。

>ユーザーフォームでの転記がすべてテキストになってしまうのが問題でした。
>そうすると転記部分のマクロをかえなければなりませんね。

テキストボックスで、どのようにいれていますか？
書式が残ってさえいれば、一旦入力した後に、Range に、Value 値を入れればよかったような気がしますね。

例えばですが、

　.Range("A6:L7").Value = .Range("A6:L7").Value　'←こういうように入れます。
　sh.Range("A2:L3002").AdvancedFilterAction:=xlFilterCopy, _
　CriteriaRange:=.Range("A6:L7"), CopyToRange:=cRng, _
　Unique:=False

補足

うまく検索されましたが、空白も検出されてしまいました。
空白を省くにはどのようにしたらよいでしょうか？

回答No.4

Wendy02

私の文章がわかりにくいでしょうか。

つまり、検索値も被検索値のデータも、みんな同じ書式やデータ型でないといけません。
ワークシート上では、同じにみえても、片方が文字型で片方が日付のシリアル値では検索はされない可能性があります。

それらを、どちらも統一したいのです。
直接入力してしまうと、その入力値は、日付なら、自動的にシリアル値に変わってしまいますから、元の被検索値側もシリアル値でなければなりません。

書式--セル--ダイアログで、数値になっているのか確認していただいたほうがよいですね。

補足

内容がようやくわかりました。理解が遅く申し訳ありません。
どうやら検索値を入力する際に、ユーザーフォームでの転記がすべてテキストになってしまうのが問題でした。ユーザーフォームで検索値をいれるのではなく、CriteriaRangeに直接入力をすると検索できました。そうすると転記部分のマクロをかえなければなりませんね。

回答No.3

Wendy02

質問の内容が分かりました。おっしゃられるとおり、セルとテキストボックスがリンクしているかもしれません。
入力するシートをユーザーフォームのリストより選択し、各シートそれぞれ最終行にデータを入力していくようにしています。
Sheets(sn).Range("E" & 最終行).Value = TextBox6
Textbox6は日付です。
検索値自体をユーザーフォームで入力するようにしているのが問題なのでしょうか？

>セルとテキストボックスがリンクしているかもしれません。
ControlSource にセルのアドレスを入れているということでしょうか？
それはお分かりになっていませんか？
もう一度、お聞きしなければなりませんが、日付値だったのでしょうか？
その部分が確認できないと、次のアドバイスが曖昧になってしまいます。

例えば、平成22年6月20日　が検索値と、2010/6/20 と、06/20 とでは、関数とは違い、うまく検索できなかったように思います。それに、下位バージョンでは、もっと違う現象が現れます。これは、昔のVBA上級問題集に出てくる質問です。

>入力するシートをユーザーフォームのリストより選択し、各シートそれぞれ最終行にデータを入力していくようにしています。

どちらでもよいことだと思いますが、確かに、
Sheets(sn).Range("E" & 最終行).Value = TextBox6.Text
のほうが、確実かもしれません。

問題は、その後のことです。

>検索値自体をユーザーフォームで入力するようにしているのが問題なのでしょうか？
そんなことはありません。

CriteriaRangeに使われる　Range("A6:L7")
マクロで処理する方法もありますが、それよりも、本来、この部分のセルの書式と、データの書式と合わせてあげるようにすればよいのです。良く調べ、試してみてください。

回答No.2

Wendy02

マクロのコードについては、動いているなら、それはそれで、今の段階ではいいのです。

私がお聞きしているのは、検索値の問題です。日付など、貼りつけたりすると、書式などが変わるものではないか、と考えています。前回の質問にもつながっていますが、UserForm で貼り付ける部分が出てきません。もしかしたら、セルとテキストボックスがリンクしているのではないかと思いました。日付の検索は、難しい部分があります。

日付や時間は、入力した値に対して、マクロで貼りつけたりする場合は、値や型の変化(キャスティング）は起こりませんが、コピー＆ペーストや手入力した時に、書式とともに表示が変わっているのではないか、と考えました。

補足

質問の内容が分かりました。おっしゃられるとおり、セルとテキストボックスがリンクしているかもしれません。
入力するシートをユーザーフォームのリストより選択し、各シートそれぞれ最終行にデータを入力していくようにしています。
Sheets(sn).Range("E" & 最終行).Value = TextBox6
Textbox6は日付です。
検索値自体をユーザーフォームで入力するようにしているのが問題なのでしょうか？

回答No.1

Wendy02

掲示したコードそのものは、貼りつけ時のミスなのか、そのままでは動くようには思いませんが、直接の質問ではありませんから、それは無視させていただきまきますが、

>他のエクセルシートからデータをコピー＆ペーストしたり、直接入力をすると検索できません。

ふつうはありえませんから、何か書式が変わったりしているのかもしれません。
検索値が、日付などではありませんか？
もしそうですと、日付自体は、小数点は混じっていませんから、検索は出来るのですが、確か、書式を合わせないといけませんから、

クライテリア部分は、マクロの起動時に、必ず、書式を合わせなくてはならないかもしれませんね。

だから、ループの中でクライテリアを取るのではなくて、ループの前に、書式をあわせて、変数にいれて、AdvancedFilter のCriteriaRange:=変数　と当てたほうがよいですね。

補足

実際のコードはこれを使用しています。
Sub 検索()
Dim Rng As Range
Dim cRng As Range
Dim sh As Variant
With Sheets(2)
.Select
Application.ScreenUpdating = False
.Range("A13:L10000").ClearContents
For Each sh In Array(Sheets(3), Sheets(4), Sheets(5), Sheets(6), Sheets(7), Sheets(8), Sheets(9), Sheets(10))
Set cRng = .Range("A65536").End(xlUp).Offset(1)
If cRng.Row < 13 Then
Set cRng = .Range("A13")
End If
sh.Range("A2:L3002").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("A6:L7"), _
CopyToRange:=cRng, _
Unique:=False

cRng.Resize(, 17).Clear

Next sh
End With
Application.ScreenUpdating = True

End Sub
検索値を指定せずに検索した場合もやはりコピー＆ペーストのデータは検索にかかりませんでした。