【VBA】別シートを検索して該当があれば『●』表示

質問

VBA初心者（独学中…）です。
別シートからVLOOKUPの要領でデータの抜き出しをしたいです。
関数を試しましたが、データ量が多く、かなり時間が掛かってしまうため、
できればマクロで完了させたいと思っています。

ご教示のほど、何卒よろしくお願いいたします。

------------------------
　帳票の仕様
------------------------
■Excel2010 にて
　・Sheet1～9のＪ列に『検索コード』があります。
　・Sheet10は【コードマスタ】シートです。
　・行はシートにより増減あり。
　・列はSheet1～9で共通、項目・並び等 変更なし。
　　　　Sheet10も項目・並びは変更しません。

------------------------
　やりたいこと
------------------------
Sheet10からSheet1～9へ、VLOOKUPのように該当データを抽出・転記したい。

------------------------
　具体的には…
------------------------
Sheet1～9の『検索コード（Ｊ列　※データは［セル：Ｊ２］以下～）』を元に、
　（１）Sheet10のＡ列を検索 ⇒ 該当があればSheet1～9のＫ列へ
　（２）Sheet10のＢ列を検索 ⇒ 該当があればSheet1～9のＬ列へ
『●』が表示されるようにしたい。

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

試しに（１）の動作テスト用に、以下を書いてみましたがうまく動作しませんでした。
（★の部分がエラーになります）

正しく動作させるには、どのようにしたらよろしいでしょうか。
（実際のコードを教えていただけますと、大変有難いです…）
================================================================
Sub コードマスタからＫ列値をVLookup()
Dim tbl As Range
Set tbl = Worksheets(10).Range("A:B")

Dim key As Long
key = Range("J2").Value　　　　　　　　★

On Error Resume Next
Dim ret As String
ret = WorksheetFunction.VLookup(key, tbl, 1, False)
On Error GoTo 0

Range("K:K").Value = ret

End Sub
================================================================

回答No.4

nishi6 · ベストアンサー

Vlookupの
　検索値：シート1～9のJ列
　範囲　：シート10のA、B列
　結果の出力：シート1～9のK、L列

と思われます。

シート10のA、B列がどのような内容か分からないのと、その後エラーチェックが行われているので、この行でエラーが出る正誤は断定できないように思えます。シート10のA、B列が数値なのに、どこかのシートのJ列に文字が入っていた場合などです。このような場合は、Longではなく、Variantで宣言すべきではないでしょうか。Keyの設定は無条件に行い、Vlookupの検索でエラーを出すように統一できます。

　Dim key As Long
　key = Range("J2").Value　　★

質問の観点とは異なるかもしれませんが、下記、上の４行ではエラーがなければ、シート10のA列の値が求まるので、５行目では「●」は表示できません。エラーの場合、「""」です。上４行でエラーチェックをしていますが、さらに表示（５行目）のときも判定が必要になっています。

　On Error Resume Next
　Dim ret As String
　ret = WorksheetFunction.VLookup(key, tbl, 1, False)
　On Error GoTo 0

　Range("K:K").Value = ret

質問にあるやりたいことを書いてみました。
当方、Win10、Excel2010です。ご参考に。

Sub コードマスタからK列値をVLookup2()
　Dim tbl(1) As Range
　Set tbl(0) = Worksheets(10).Range("A:A")　'// A列
　Set tbl(1) = Worksheets(10).Range("B:B")　'// B列

　Dim sht As Integer　 '// ワークシート
　Dim key As Variant　 '// 検索キー
　Dim col As Integer　 '// 列カウンタ
　Dim rg As Long　　　 '// 行カウンタ
　Dim ret As String　　'// Vlookup検索結果
　Dim ret2 As String　 '// 検索結果表示

　For sht = 1 To 9
　　With Worksheets(sht)
　　　For rg = 2 To .Range("J" & Rows.Count).End(xlUp).Row
　　　　For col = 0 To 1
　　　　　key = .Range("J" & rg).Value
　　　　　ret2 = "●"

　　　　　On Error GoTo ErrorTrp
　　　　　ret = WorksheetFunction.VLookup(key, tbl(col), 1, False)
　　　　　.Range("J" & rg).Offset(0, col + 1) = ret2
　　　　Next
　　　Next
　　End With
　Next

　Exit Sub
ErrorTrp:
　ret2 = ""
　Resume Next
End Sub

回答No.5

imogasi

質問者のシート場合は無視しているが、
該当が多数ある場合に、エクセル関数で検索を続けるのはＶＬＯＯＫＵＰ関数より、ＭＡＴＣＨ関数が考えやすいだろう。ＶＬＯＯＫＵＰ関数は、見つかった行を捉えにくいから。
参考までにやってみたので、挙げてみます。
ーー
例　データ
Ａ列　Ｂ列
東京　 鈴木
神奈川 　大野
東京 　木下
神奈川　 上野
千葉 　加藤
東京　 田中
東京 　山下
千葉 　大倉
東京 　富田
東京 　森野
千葉 　神川
ーーー
府県　東京のものの氏名を表示してみる。
標準モジュールに
Sub test03()
Set sh1 = Worksheets("Sheet1")
lr = sh1.Range("A100000").End(xlUp).Row
'--検索語
x = "東京"
s = 1 '検索スタート行
'----
p1:
On Error GoTo Err
Z = WorksheetFunction.Match(x, sh1.Range("$A$" & s & ":$A$" & lr), 0)
’セル範囲の相対的な見つけた第行目の行かを求める
'今見つかった行を第1行目からの行数に換算すると　s + Z - ¹行
MsgBox sh1.Cells(s + Z - 1, "B") & " " & (s + Z - 1)　'氏名を表示
s = s + Z - 1 + 1 '一行下の行を検索範囲の先頭行とする
GoTo p1
'---
Err:
MsgBox "これ以上は見つからず"
End Sub
もし東京の行に●を入れるなら
sh1.Cells(s + Z - 1, "Ｃ")="●"
を入れる。
東京 　鈴木 ●
神奈川 大野
東京 　木下 ●
神奈川 上野
千葉 　加藤
東京 　田中 ●
東京 　山下 ●
千葉 　大倉
東京 　富田 ●
東京 　森野 ●
千葉 　神川
本番ではＭｓｇＢｏｘ行は削除する。

回答No.3

imogasi

関数学習中からＶＢＡに入ったのだろうが、関数のことは忘れる方がよい。
検索はＶＬＯＯＫＵＰやＭＡＴＣＨ関数をＶＢＡで利用よりも、Ｆｉｎｄ、ＦｉｎｄＮeｘｔを使うべきだ。
ただし、対象が範囲内に複数ある場合は、すべてを探すロジックはわかりにくいが。
Ｇｏｏｇｌｅなどで、「エクセル　ＶＢＡ　Ｆｉｎｄ」で照会して、よさそうな記事をゆっくり時間をかけて学習すべきだ。
＝＝
それにＶＢＡの何かを学習するときに、現実の問題（この質問に書いている、ごたごたした状況（シートのどれとどれとか、範囲のどこどこ等）を一緒に学習しようとしない方がよいと思う。
　１つずつ分けて考えられるように、早くならないと。
全部の状況を書いて、丸投げし、回答をそっくりいただき、しようとしているようだが。
課題の主題は検索だろう。
＝＝＝
http://officetanaka.net/excel/vba/cell/cell11.htm
https://www.moug.net/tech/exvba/0050116.html
・まず検索する
・見つからない場合
・次の該当を見つける　FindNext
・すべて該当を検察し終わったかの判定
（グルグル回り検索をしてしまうので、最初に見つかったセルに帰っていたら
、全部探し尽くしたとして、打ち切る）
などについて、勉強のこと。
＝＝
こんなことを言っても、（他の質問の経験だが）回答者の言うほうに方針転換をした質問者は少ないようだ。しかし上達には柔軟さが大切と思う。

回答No.2

Mathmi

keyに代入したいJ2セルはどのシートのJ2セルでしょうか？
現状のコードではシートの指定がないので、Worksheets(10)ではなく、アクティブシートのJ2セルを参照しています。
そのセルに文字列が入っていれば、それがエラーの原因でしょう。

回答No.1

kkkkkm

> key = Range("J2").Value
keyは数値で宣言されてますがJ2は数値でしょうか
「型が一致しません」というエラーならJ2が数値ではないと思います。