Exel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列　　　　 B列　　　　　C列　　　　　　
商品名　　商品番号　　コード
モータ　　U-1325-L　　
ホルダ　　R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル（B列のコード）の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
　　Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

No.4 ベストアンサー 回答者： avanzato 回答日時： 2009/12/19 13:59

こんにちは。

とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
　Application.ScreenUpdating = False
　Dim I As Long
　Dim xlBook
　Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
　I = 2
　Do While Range("A" & I).Value <> ""
　　ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
　　I = I + 1
　Loop
　xlBook.Close
　Application.ScreenUpdating = True
　MsgBox ("完了")
End Sub

この回答へのお礼

検索のセル範囲を指定する　Range("A2:B65535"）
を参考にさせていただきました
ありがとうございました

みなさんのアドバイスを組合わせて、単語の意味を検索しながら
思い通りのものができました!
みなさまに感謝いたします。

お礼日時：2009/12/22 17:12

No.3 回答者： hige_082 回答日時： 2009/12/19 12:41

＞基本事項は本で学びましたが、呪文のようなコードはよく理解できません。

呪文は分らないようなので
基本的なコードしか使用していません

'サンプルコード
Sub test()
Dim x As Long, y As Long
For x = 2 To Workbooks("book1.xls").Worksheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For y = 2 To Workbooks("book2.xls").Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
If Workbooks("book1.xls").Worksheets("sheet1").Cells(x, 2).Value = Workbooks("book2.xls").Worksheets("sheet1").Cells(y, 1).Value Then
Workbooks("book1.xls").Worksheets("sheet1").Cells(x, 3).Value = Workbooks("book2.xls").Worksheets("sheet1").Cells(y, 2).Value
Exit For
End If
Next y
Next x
End Sub

しかし、呪文を使用しないと長くなりますね
後は自分の環境に合わせてアレンジしてください
勉強頑張って

この回答へのお礼

よく理解できました!

Dimは、まとめて宣言できるものなのですね?
今までは、それぞれ行を変えて宣言していたので、
何行にもなっていました…。

また、元気にがんばれます。
ありがとうございました。

お礼日時：2009/12/22 08:45

No.2 回答者： imogasi 回答日時： 2009/12/18 20:19

>関数ではなく、マクロで処理を希望します

生半可な知識で言ってませんか。
関数ＶＬＯＯＫＵＰでやれる。
ＶＢＡでもＶＬＯＯＫＵＰ関数は使える。
それを知った上で言ってますか。
ーー
それ（いや）なら、ややこしいほうのＦｉｎｄメソッドを勉強したら良い。（背伸びすることは無いと思うが。時間をかけて勉強しないと。入門本を一読して達せられるものではない。）
ＷＥＢには「ＶＢＡ　Ｆｉｎｄ　ＦｉｎｄＮｅｘｔ」で照会すればコード例が沢山在る。
普通は、ＡｕｔｏＦｉｌｔｅｒを使うものではない。

この回答への補足

ネットで質問すると
ほんとうに勉強になって感謝の気持ちで一杯になるときと、
質問しなければ良かった、といやな気持ちになる両極端なことが起こります。
それでも、仕事上どうしても必要なときは嫌なことも我慢して質問しています。

相手の状況を良く知りもしないで、馬鹿にするような言い方はほんとうに失礼だと思う。何様なんでしょう。
こんな馬鹿な質問で頭にくるんなら、見ないで無視すればいいでしょう。

この質問は、今回組んだマクロの、ほんの一部の動作です。
何百枚の表を、(1)図番を規則的に書き換える、(2)セレクトケースで場合わけする、(3)別の一覧から書き換えた番号に該当する関連項目を引っ張ってくる、(4)新しい図番番号で保存する　など。
他のものは組めたので、最後に残った今回の件をマクロに組み込みたかったのです。

補足日時：2009/12/22 08:31

No.1 回答者： NYAN99 回答日時： 2009/12/18 15:23

マクロでやりたいなら、ツール、マクロの記録で実際にやりたいことを

やって記録終了とすると、自動でマクロが書かれていますので
それを見て修正していくと分かりやすいです。
でもKeyと一致する項目を探して、取得したいのであるなら
エクセル関数で簡単に出来ますけど、Vlookupとか
それが同じブックにあるのかないのかの違いなので、
Workbooks("ブック名").Worksheets("シート名").cells(セルの位置)で指定すればいいのでは。