エクセルVBAでFindを使った検索について

質問

エクセル2003でVBAを勉強し始めたものです。
findを用いた検索についてご教授をお願いしたく、ご質問させていただきます。

まず、
＜シート１＞
A列：受付No.
B列：氏名
C列：物件名
＜シート２＞
A～F列：省略
G～O列：物件名

とあります。

シート１上にコマンドボタンがあり、クリックするとシート１への入力用フォームが開きます。
さらに、そのフォーム内のテキストボックスに物件名を入力するのですが、テキストボックス内でダブルクリックで、物件検索用のフォームが開きます。

物件検索用フォーム内のテキストボックス（Text物件名検索）に文字列を入力し、コマンドボタン（command物件名検索）をクリックすると、フォーム内下部のリストボックス（List物件検索結果）内に、シート２のG～O列を検索した結果が並ぶようになっています。
その候補の中から選択したものが、シート１の物件名の列に並ぶようにしたいのです。

そこで、エクセルファイルを開いたすぐ後は、検索結果が意図通りに表示するのですが、一度他のコード（マクロにてシート１の行削除等）を実行した後、再度物件検索を行うと、検索結果が“なし”（その場合は「見つかりませんでした」とメッセージボックスが開くようにしてあります）となってしまいます。

変数の扱いがわるいのでしょうか？・・・

どうぞご教授のほどよろしくお願いいたします。

以下、検索用フォームのテキストボックス入力後、コマンドボタン(command物件名検索）をクリックしたときの処理コードです。

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

Private Sub command物件名検索_click()

Dim bname As String
Dim fndrange As Range
Dim firstcell As String

bname = Text物件名検索.Text

Set fndrange = Sheets("TBオーナー").Columns("g:o").Find(bname)

If bname = "" Then

MsgBox ("キーワードを入力してください")

Exit Sub

Else

If fndrange Is Nothing Then

MsgBox ("見つかりませんでした")
Text物件名検索.Text = ""
Text物件名検索.SetFocus

Else

If Not fndrange Is Nothing Then

firstcell = fndrange.Address

Do

Set fndrange = Sheets("TBオーナー").Columns("g:o").FindNext(fndrange)

List物件検索結果.AddItem fndrange.Value

Loop While Not fndrange Is Nothing And fndrange.Address <> firstcell

End If

End If

End If

End Sub
-----------------------------------------

また、関係あるかわかりませんが、他のコード（シート１から行を削除するマクロ）も掲載させていただきます。

以下、

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

Private Sub Command削除_Click()

Dim t As Long
Dim DelNo As String
Dim delNos As Long
Dim s As Range

DelNo = InputBox("削除するデータＮＯを入力してください")

delNos = Val(DelNo)

Set s = Sheets("TBマスター").Columns("A").Find(delNos, lookat:=xlWhole)

If DelNo = "" Then

Exit Sub

ElseIf s Is Nothing Then

MsgBox ("データがありません")

Exit Sub

Else

t = Sheets("TBマスター").Columns("A").Find(delNos, lookat:=xlWhole).Row

Rows(t).Delete

End If

End Sub

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

質問のが悪いかも知れませんが、必要なことがあれば随時追記させてください。

以上、よろしくお願いいたします。

回答No.3

Musaffah · ベストアンサー

再度#2です。

質問者様のコードを基に適当に環境作って動かしてみたんですが、Nothingになることはありませんね。
「行削除によって本当に検索文字列がなくなった」というオチはありませんか？

また、2回連続して（いったん物件検索フォームを閉じて再度表示させて）「command物件名検索_click()」を実行するとどうなります？

お礼

解決いたしました！！
原因は、行削除時のコード内の、『findメソッド』の引数“lookat:=xlwhole”が、次に物件名検索するときのfindの引数として維持されていたようです。物件検索時のfindは部分一致で行いたかったのです。が、その旨、説明をしおておけば、回答者様へもわかりやすく質問ができたものと、反省しております。この度は本当にありがとうございました。また、日々勉強していきたいと思います。

補足

ご返答いただきありがとうございます。

＞「行削除によって本当に検索文字列がなくなった」というオチはありませんか？

削除に関する説明が不足していたようで、申し訳ございません。補足させていただきます。

まず、シート１には

A列　　　B列
NO　　　物件名
１　　　　物件○○
２　　　　物件□□
３　　　　物件△△
　　　　　・
　　　　　・　
　　　　　・
こんな感じで並んでいます。

処理の流れとしては、

シート１上のcommand削除クリック
↓
インプットボックス表示
↓
インプットボックスに入力されたNOを、シート１のA列から検索
↓
検索された値を含む行番号を取得
↓
その行全体を削除

という感じです

“削除”するのは、シート１の縦方向に並んだデータの1行のことです。検索されるデータのあるシート２については削除される部分はないので、検索文字列がなくなったということはないです。

＞また、2回連続して（いったん物件検索フォームを閉じて再度表示させて）「command物件名検索_click()」を実行するとどうなります？

問題なく動作いたします。

また、削除を実行すると、その後検索できなくなるとご説明しましたが、削除実行しなくとも、つまり、削除時にNOを指定するための、インプットボックスを表示し、キャンセルをするだけでも、その後検索ができなくなることが判明しました。

とにかく、もういちど初めから作り直してみようと思います。いろいろと教えていただき本当に感謝しています。

回答No.2

Musaffah

［F9］（ブレークポイント）と［F8］（ステップ実行）を用いて、ご自分でデバッグことをお勧めします。

例えば、
bname = Text物件名検索.Text
の行にブレークポイントを設定した後に、普通に操作して下さい。
ブレークポイントを設定した行で一時中断となります。
そこで（［F8］を押しながら）1行ずつプログラムを実行していくと、具体的にどこがおかしいかが明確になると思います。
その上でどうしても「何故このような動き（値）になるかわからない」となった場合に質問されると、より具体的に質問できます。（＝皆も回答しやすい質問になると思います。）

的を得た答えにはなっていないと思いますが、デバッグもプログラミングをしていく上で必要な技術です。
これもVBAの勉強だと思ってチャレンジしてみて下さい。

補足

ご回答ありがとうございます。
早速教えていただいたデバッグについて、行ってみました。
結果、
Set fndrange = Sheets("TBオーナー").Columns("g:o").Find(bname)
のところで、bnameには文字列が入っているのですが、fndrangeの値が、“nothing”となってししまうことが、問題とわかりました。1回目の検索時はしっかりと検索結果が入るのですが・・・

回答No.1

imogasi

>シート１への入力用フォームが開きます。
このフォームとはユーザーフォームのことか。
シートの中のセル範囲をフォーム（入力書式を持った様式）と見立てたのか。
＞Ｓｈｅｅｔ１のA列：受付No.
B列：氏名
C列：物件名
のことか？
ＶＢＡでフォームというと（ユーザー）フォームのことを言うことが多いので注意。
＞シート１への入力用フォームが開きます。
というのも違和感がある。シート１が選択された常態になるということか
＞シート１への入力用フォームが開きます。
これも、フォームとはユーザーフォームのことか。シートのセル範囲のことか？
ーーー
もう少し質問の課題を絞って質問できませんか。
質問者がやろうとしている、シートやフォームの構成がよくわからない。
ーー
コードを載せる前に、下記仕組みなどを明確に質問すること（むしろこの設計を質問して洗練させるべきと思うが）
検索条件（検索語）を入力する場所（シート？フォーム？）
検索実行のトリがーーコマンドボタン（第１段のトリガ）らしい
検索するデータのあるシートは何処（シート・列）？
検索が２段構え、らしいが　（検索語入力ー検索ーリストボックス表示ー選択ー検索ー結果をシート？
こういう構成をはっきりと質問に書くべき。
第２段のトリがはコマンドボタンらしい＝＞ＬｉｓｔＢｏｘの選択クリックで良いのでは。
結果はシート？どこのシート？
など
ーー
コードも丸投げコピーしただけで、質問者の検討がなされておらず、回答者の負担が大きいのでは。

お礼

VBAをはじめたばかり＆はじめての投稿で、至らないことばかりでしたが、いろいろと教えてくださって本当にありがとうございました。今後のためにも非常に勉強になりました。

補足

ご回答ありがとうございます。質問の仕方について、配慮がかけていたようですみません。その辺ご教授いただき、今後の勉強になります。
さて、

全体の流れとしましては、

シート1への入力用ユーザーフォームをシート１上に設置したコマンドボタンにて開く
↓
入力用ユーザーフォーム内のテキストボックスをダブルクリックし、物件検索用ユーザーフォームを開く
↓
物件検索用ユーザーフォーム内のテキストボックス（Text物件名検索）に検索語を入力し、コマンドボタン（command物件名検索）クリック
↓
検索するデータのあるシート２のG列～O列から検索
↓
物件検索用ユーザーフォーム内のリストボックス（List物件検索結果）に、検索結果が表示される
↓
リストボックス内のアイテム選択し、入力用ユーザーフォーム内テキストボックスに入力
↓
入力用ユーザーフォーム内、登録ボタン（コマンドボタン）クリックで、シート１の物件名（C列）に入力。

以上、こんな流れです。※“フォーム”とは全てユーザーフォームのことです。明確にしておくべきでした。

設計についてまず勉強が必要ですね・・・おっしゃるとおり（＾－＾）
もっと回答者への配慮がなされ、うまく質問ができるように頑張ります！