マクロで非表示シート内にあるデータの検索方法が知りたい

質問

マクロの勉強をしているのですが、練習で郵便番号検索ツールを作成したいと思っています。

概要
　まず、郵便番号7桁を入力する。変換ボタンを押すと、住所を検索し、漢字で住所を表示し、読み仮名をひらがなで表示する。

とまぁ、こんな感じのものを作りたいと考えています。
で、取りあえず郵便番号と住所（読み仮名つき）一覧表を作りました。

一覧表は・・
A1には郵便番号、A2には都道府県名、A3には市区町村名、A4には町名、A5には市区町村名（ふりがな）、A6には町名（ふりがな）
となっています。

さて、ここからが問題なのですが・・・
ユーザーフォームを使ってこれを使えるようにしたいのですが、検索方法がわかりません。

イメージとして、テキストボックスに入力してもらい、変換ボタンを押すと、エクセルシートの中から該当する郵便番号を検索し、その隣にある内容を変数に代入し、持ってこようと思っていました。

でも、ここで問題が・・・
検索してるところが見えるとかっこ悪い　笑
というわけで・・もうちょっと何とかしたいんですけど、シートを非表示にしちゃうと、マクロの記憶で得た検索方法はエラーになっちゃうんですよね。。

そんなわけで、これをどうにかできないか考えています。非表示シートの検索方法があれば一番いいのですが、もしない場合は代替案を教えていただけるとありがたいです。
よろしくお願い致しますm(_ _)m

回答No.3

noname#187541 · ベストアンサー

こんにちは。

検索の前に一覧表の作り方ですが、行と列を逆にした方がいいです。

データ数がどのくらいか分かりませんが、項目が行方向ということはデータは列方向と言うことですよね？列は256しかないのですぐ足りなくなるのでは？と思います。

検索方法ですが、一般機能で言うと　編集→検索　をマクロで行っているのでしょうか？

だとすれば見つけたセルがアクティブになるので「マクロの記録」で得たコードでは検索部分のコードの最後にActivateと付いていると思います。

Activeにするのではなくて、Range型のオブジェクト変数に検索結果のセルを入れてっこの変に対して処理をするようにすればいいと思います。

こんな感じです。例えばSheet1でcを検索しその右隣の値を表示します。

Dim myrng As Range

Set myrng = Worksheets("Sheet1").Cells.Find(What:="c", _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:=False, _
MatchByte:=False, SearchFormat:=False)

MsgBox myrng.Offset(0, 1).Value

どうでしょうか？

お礼

あ、ズバリでした。すごい。。
ありがとうございます。
今回はこの方法で行きたいと思います。

ご解答いただいた皆様
ありがとうございました。

補足

書き方間違いました＾＾；
A1、B1、C1と列方向ですね。。
データ件数が12万件くらいなので行方向に項目を
割り振ったらシートがいくつあっても足りません　笑
ご指摘ありがとうございます＆失礼しました。

回答No.2

imogasi

＞シートを非表示にしちゃうと、マクロの記憶で得た検索方法はエラーになっちゃうんですよね
今日朝に回答した、別質問の回答を少しいじくって、
Sub test01()
Worksheets("Sheet1").Visible = xlVeryHidden
' Worksheets("Sheet1").Visible = True
Worksheets("Sheet1").Activate
On Error GoTo err
cd = InputBox("コード")
dt = CDate(InputBox("日付"))
d = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
i = Worksheets("Sheet1").Range(Cells(2, "A"), Cells(d, "A")).Find(cd).Row
j = Worksheets("Sheet1").Range("B1:AJ1").Find(dt).Column
MsgBox Worksheets("Sheet1").Cells(i, j)
Exit Sub
err:
MsgBox "見つかりません"
End Sub
を実行すると、うまく検索しましたが。
私の思い違いがあるかも知れませんので、質問者の自己テストや後続の回答によってチェックしてみてください。
もしOKなら、Findメソッド、Offset、シートへ実行用コマンドボタン貼り付けで（フォームを使わなくても）できると思います。

お礼

今回はアクティブにしないことによってエラーが無事解消できました。
教えて頂いた書き方も試してみようと思います。
ありがとうございました

回答No.1

ForestRain

こんにちは。
現在どのようにして、検索しているのか判りませんが、
非表示のシートをアクティブにしようとしていませんか？
例えば、Sheet1が非表示だとして
Sheets("Sheet1").Select
や
Sheets("Sheet1").Activate
などとしていませんでしょうか？

Find メソッドはシートをアクティブにしなくても
Set r = Sheets("Sheet1").Cells.Find(What:=検索値)
のようにして検索値の有るセルを取得できますよ
。

お礼

ありがとうございます。マクロの記憶を利用していたので、ズバリアクティブにするにしていました　汗

これで無事に検索できるようになると思います。
ありがとうございました