検索値と検索先のシートが別でもOKなVBAはありますか？

質問

「Ｅｘｃｅｌ２００２」を使用して、内線番号表を
作ろうと思っています。

「表紙」sheetにあるコンボボックスで選んだ
値（各部署名）が、
「内線番号表」sheetの「Ｅ１」に入るとします。
（「LinkedCell：内線番号表!E1」）
その「Ｅ１」を検索値として「内線番号表」内を
探すマクロを、フォームボタンに登録しました
（以下。自分で書いたわけではないのですが）。

Sub 部署を表示()

With Worksheets("内線番号表")
Sheets("内線番号表").Select

Cells.Find(What:=Range("E1"), After:=Cells(2, 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, MatchByte:=False, SearchFormat:=True).Activate

End With

End Sub

これはこれでうまくいったのですが、少し問題が
出てきてしまいました。

「内線番号表」sheetはデータを取り込んで、
列や行を大幅にいじる事が頻繁にあるので、
コンボボックスの「LinkedCell」として指定した
「内線番号表!E1」という
アドレスが勝手に変更されてしまうのです。
（「$」をつけても駄目でした）

そこで、コンボボックスの「LinkedCell」を、
いじる事がない「表紙」sheetの
セルに変えたいと思うのですが…
「検索先のシートの中に検索値がなくても
検索出来る」方法はあるのでしょうか？

つまり、「表紙」sheetのセルの値を元に、
「内線番号表」sheetを検索するＶＢＡのコードが
あれば、教えて頂きたいのですが…。
（これが出来れば、他にも応用したいと思うので）

コントロールＢＯＸやＶＢＡは勉強し始めた
ばかりなので、うまく説明出来たかどうか
分かりませんが…よろしくお願い致します。

回答No.4

noname#29107 · ベストアンサー

問題を理解しているか自信がありませんが、
VBAの変更は、
Cells.Find(What:=Sheets("表紙").Range("E1"), ｛以下略｝
の1カ所でいいんじゃないですか？

お礼

お礼が遅くなって申し訳ありません。

これでうまくいきました！

こんな簡単な事でよかったんですね。
スミマセン。助かりました。

回答、ありがとうございました。

回答No.6

imogasi

なんか問題文が長くて判り難いが
（１）ＶＢＡでやるならLinkedCellなど使わなくて良い。
セルを余分に使うだけです。
変数（仮にbusho）に入れ（代入す）れば良い。
Private Sub ComboBox1_Click()
busho = ComboBox1.Text
MsgBox busho
End Sub
を作れば、選んだ項目ＩｔｅｍがMSGBOXに表示されますので、納得出来るでしょう。
そして検索のコードで
Cells.Find(What:=Range("E1"), ・・を
Cells.Find(What:=busho,
にすれば良いのでは。
むしろCELLSがどのシートか指定せねばならない。
fINDメソッドのOBJECT(この概念が判るかな）はセル範囲Rangeで、特定のSheetを指定しないとならず、ブック全体は１度に対象に出来ないと言うこと。
選択部署と言うセルを設け、表示したい（そして挿入などで動く）のなら、見だしの選択部署を設け、選択部署と言う語句を探し、その下のセルなりにCells(i,j)=bushoとすれば済む。
行挿入で問題なのはListFillRangeの事ではないですか。
間に列挿入されると空白行が入ってしまう。
＞検索先のシートの中に検索値がなくても検索出来る」方法はあるのでしょうか
VBAが同一ブックの他シートも扱えるので、変数に検索語を
いれてしまえば、変数がどこのシートのセルから取ったものであろうとiNPUTBOXで入力させたものであろうが関係ない。だからこう言う質問は成り立たないのでは。
＞コントロールＢＯＸやＶＢＡは勉強し始めたばかりなので、うまく説明出来たかどうか
扱う（データ値を取得したり、設定したりするセルの）シートを２つ以上扱う場合のやり方を勉強してください。
一般論として
変に我流のvbaコードを載せて、その改良やバグ取りを依頼せずに、vbaコードなどを混ぜずに、やりたいこととシートの状態を説明して、やり方は回答者にまかせ、どれくらい自分の構想と回答がレベルの差があるのかないのか勉強するのが、進歩する上で良いと思いますが。

お礼

お礼が遅くなって申し訳ありません。

私はimogasiさんの回答にはまだついていけない
レベルで…スミマセン。

仕事上、必要に迫られてやるものですから、
どうしても「出来上がりさえすればいい」
という風になりがちで、「マクロの自動登録」や
他の社員が作ったもの、ネットで紹介されている
もののアレンジに終始してしまい、
基本的な勉強が後回しになっているのが現状です。
（それほど難しいものは求められないので…）

でも確かにこのままじゃ人頼みばかりに
なってしまって駄目ですよね。頑張って勉強します。

回答、ありがとうございました。

回答No.5

KenKen_SP

ご希望の動作をしない原因は、No.4方が回答されているとおりです。

補足ですが、Sheets("内線番号表").Select によりアクティブシートが[内線番号表]となっていることをご確認下さい。

つまり、Range("E1")のようにシート名を省略して検索語を指定した場合、マクロの解釈は、シート[内線番号表]のE1セルとなっていますね。

別件ではありますが、アドバイスです。

＞With Worksheets("内線番号表")
＞Sheets("内線番号表").Select

では With の意味がありません。HELPをご覧下さい。

お礼

お礼が遅くなって申し訳ありません。

何分勉強を始めたばかりで、基本的な事も分からず
聞いてしまってスミマセンでした…。

丁寧な解説、ありがとうございました。

回答No.3

noname#8445

＃１の方がいいかも

（・・；）ごめんなさい・・

回答No.2

noname#8445

すんませんエクセルが入って無くて穴だらけ

そのまま拝借させてもらって

Sheet1にボタンを作ります
右上の　_□×　あたりの灰色の場所で右クリック
　　「フォーム」を選びます
セル上でドラッグして

Sub 部署を表示()

にリンクを張ります

以下マクロ

Sub 部署を表示()

on　error　goto errora:

Worksheets("内線番号表").Sheets("内線番号表").Cells.Find(What:=Range("E1"), After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, MatchByte:=False, SearchFormat:=True).Activate

exit sub

errora:
msgbox("データがありません。")
end sub

でどうでしょう
E1は検索元です（要変更）

お礼

お礼が遅くなって申し訳ありません。

マクロを試させて頂いたのですが、
「内線番号表」を選択していないと
「データがありません」となってしまいました…。

私のやり方が悪かったのでしょうか。
スミマセン。

回答、ありがとうございました。

回答No.1

noname#8445

そのまま拝借させてもらって

Sheet1にボタンを作ります
右上の　_□×　あたりの灰色の場所で右クリック
　　「フォーム」を選びます
セル上でドラッグして

Sub 部署を表示()

にリンクを張ります

以下マクロ

on　error　goto errora:

Worksheets("内線番号表").Cells.Find(What:=Range("E1"), After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, MatchByte:=False, SearchFormat:=True).Activate

exit sub

errora:
msgbox("データがありません。")
end sub

でどうでしょう
E1は検索元です（要変更）