ここから本文です

エクセルVBAオートフィルタで検索値が1件もヒットしなかった場合の処理の方法に...

rib********さん

2012/6/111:20:20

エクセルVBAオートフィルタで検索値が1件もヒットしなかった場合の処理の方法について

インプットボックスに商品名を入れてフィルタで検索します。インプットボックスに何もいれずにキャンセルした場合そのまま閉じる、何もいれずOKを押したら MsgBox "検索する語句を入れてくださいと出る。これは思うようにできているのですが、インプットボックスにいれた文字がヒットしなかった場合、今は表がすべてなくなって項目名っだけが残ってしまうのですが、ヒットしなかった場合 MsgBox 検索値がありません。と出てフィルターをかける前のすべて表示されている状態にしたいのですが、この部分をどうしたらよいか教えてください。現在の状態は下記のようになります。検索行はSUBTOTAL(3,C3:C65536)でAH1のセルに入っています。よろしくお願いします。
Sub 商品検索()
Dim i As String
ActiveSheet.Unprotect
i = Application.InputBox(Prompt:="名前入力", Type:=2)
If i = "False" Then
ActiveSheet.Range("A2").Select
ActiveSheet.Unprotect
Selection.AutoFilter
Else
If i = "" Then
MsgBox "検索する語句を入れてください。"
Else
Selection.AutoFilter Field:=14, Criteria1:="*" & i & "* "
End If
ActiveSheet.Protect
End If
End Sub

閲覧数:
2,522
回答数:
3
お礼:
100枚

違反報告

ベストアンサーに選ばれた回答

por********さん

2012/6/112:37:18

Sub 商品検索()
Dim i As String
ActiveSheet.Unprotect
i = Application.InputBox(Prompt:="名前入力", Type:=2)
If i = "False" Then
ActiveSheet.Range("A2").Select
ActiveSheet.Unprotect
Selection.AutoFilter
Else
If i = "" Then
MsgBox "検索する語句を入れてください。"
Else
Selection.AutoFilter Field:=14, Criteria1:="*" & i & "* "
gyo = Range("A" & rows.count).end(xlup).row '<=== 追加6行
if gyo = 2 then
msgbox "検索値がありません"
Selection.AutoFilter Field:=14
activesheet.autofiltermode = false
end if
End If
ActiveSheet.Protect
End If
End Sub

フィルターを実行したときに、セルカーソルがどこまで上に上がるのか、を、確認します。
(フィルターを実行前に最終行を把握しておいて、その行のA列で確認、という方法が良い場合もあります)

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

har********さん

2012/6/113:41:49

オートフィルタを1行だけに掛ける場合は、例えばN列だけにオートフィルタを掛ける場合は

Columns("N").AutoFilter Field:=1, Criteria1:="*" & i & "* "

になります。
その場合、N列のオートフィルタの結果で表示されている数は、1行目の見出しも含めて

ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count

で求まります。
これはオートフィルタかけた範囲(AutoFilter.Range)で表示している(SpecialCells(xlCellTypeVisible))セル数(Count)を表示します。
これが1の時は見出しだけなのでデータ(2行目以降)は0になります。
質問の

>Selection.AutoFilter Field:=14, Criteria1:="*" & i & "* "

は列数が不定で複数列のAutoFilterで表示しているセルの、その時のオートフィルタ対象範囲(AutoFilter.Range)の列数(Columns.Count)で割ると1列の表示数が出ます。
つまり表示数をnとすると

n = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count

または長い部分をWithにして

With ActiveSheet.AutoFilter.Range
n = .SpecialCells(xlCellTypeVisible).Count / .Columns.Count
End With

でもとまります。




という訳で質問の場合は、質問のプログラムの

>Selection.AutoFilter Field:=14, Criteria1:="*" & i & "* "

の後に


If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count = 1 Then
Selection.AutoFilter
End If


または


If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count = 1 Then
ActiveSheet.AutoFilterMode = False
End If


を追加してみてください。

tss********さん

2012/6/112:26:25

ワークシート関数を使用する方法はどうでしょう?

MsgBox "検索する語句を入れてください。"
の下に

ElseIf WorksheetFunction.CountIf(Range("C3:C65536"), "*" & i & "*") = 0 Then
MsgBox "検索する語句がありません。"

を入れてみてください。

検索する範囲が違いましたら変更してください。

Range("C3:C65536"), の部分です。

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる