こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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


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


------------------------
 具体的には…
------------------------
Sheet1~9の『検索コード(J列 ※データは[セル:J2]以下~)』を元に、
 (1)Sheet10のA列を検索 ⇒ 該当があればSheet1~9のK列へ
 (2)Sheet10のB列を検索 ⇒ 該当があればSheet1~9のL列へ
『●』が表示されるようにしたい。

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


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

正しく動作させるには、どのようにしたらよろしいでしょうか。
(実際のコードを教えていただけますと、大変有難いです…)
================================================================
Sub コードマスタからK列値を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
================================================================

投稿日時 - 2019-11-29 14:48:20

QNo.9684489

困ってます

質問者が選んだベストアンサー

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  ★

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

 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

投稿日時 - 2019-11-29 18:52:28

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

質問者のシート場合は無視しているが、
該当が多数ある場合に、エクセル関数で検索を続けるのはVLOOKUP関数より、MATCH関数が考えやすいだろう。VLOOKUP関数は、見つかった行を捉えにくいから。
参考までにやってみたので、挙げてみます。
ーー
例 データ
A列 B列
東京 鈴木
神奈川 大野
東京 木下
神奈川 上野
千葉 加藤
東京 田中
東京 山下
千葉 大倉
東京 富田
東京 森野
千葉 神川
ーーー
府県 東京のものの氏名を表示してみる。
標準モジュールに
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, "C")="●"
を入れる。
東京 鈴木●
神奈川大野
東京 木下●
神奈川上野
千葉 加藤
東京 田中●
東京 山下●
千葉 大倉
東京 富田●
東京 森野●
千葉 神川
本番ではMsgBox行は削除する。

投稿日時 - 2019-11-29 21:38:38

ANo.3

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

投稿日時 - 2019-11-29 17:45:31

ANo.2

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

投稿日時 - 2019-11-29 15:45:10

ANo.1

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

投稿日時 - 2019-11-29 15:26:34

あなたにオススメの質問