VBAのVLOOKUPで複数列を検索できますでしょうか。

Sheet1のA列に検索値として都道府県コードを入力し、Sheet２の検索範囲としてA列都道府県コード、B列都道府県名、C列県庁所在地から、都道府県名と県庁所在地を検索するのに苦戦しています。
現状B列都道府県名を検索する下記コードまではたどりつきましたが、C列県庁所在地を表示することができません。

Sub vlookup()
Dim workSh, prefSh As Worksheet
Set workSh = ThisWorkbook.Worksheets("Sheet1")
Set prefSh = ThisWorkbook.Worksheets("Sheet2")

'検索範囲を指定する
Dim prefRng As Range
Set prefRng = Range(prefSh.Cells(2, 1), prefSh.Cells(48, 2))

Dim workEndR, workTmpR As Long, tmpStr
workEndR = workSh.Cells(Rows.Count, 1).End(xlUp).Row

'VLookupでSheet1に入力された都道府県コードから、Sheet2の指定した範囲から都道府県を求める
'発見できなかった場合エラーとなりマクロが停止するので、On Errorステートメントで制御する
For workTmpR = 2 To workEndR
tmpStr = workSh.Cells(workTmpR, 1).Value
On Error Resume Next
workSh.Cells(workTmpR, 2).Value = Application.vlookup(tmpStr, prefRng, 2, False)
If Err <> 0 Then
workSh.Cells(workTmpR, 2).Value = "ERROR"
Err.Clear
End If
Next
End Sub

お知恵をお貸しください。よろしくお願いいたします。

No.5 ベストアンサー 回答者： しん14 回答日時： 2019/03/04 23:47

こんばんは

今までのご回答も拝見しましたが、C列にも県庁所在地を表示したいということだけであれば、
On Error 以下をこのように修正するだけで十分かと思います。

workSh.Cells(workTmpR, 2).Value = Application.vlookup(tmpStr, prefRng, 2, False)
workSh.Cells(workTmpR, 3).Value = Application.vlookup(tmpStr, prefRng, 3, False)
If Err <> 0 Then
workSh.Cells(workTmpR, 2).Value = "ERROR"
workSh.Cells(workTmpR, 3).Value = "ERROR"

まず、Vlookup関数は
・検索対象を(tmpStr)
・指定した検索範囲(prefRng)の左端列から検索、行を特定し
・左端列から指定した数の列数(2または3)のデータを戻り値として返す
という関数です。

他の方が関数の意味を考えるようご指摘なさっているのは、これをきちんと理解していれば
この修正はもっとも単純に思いつくことであろうということからでしょう。

また、行う作業がこれだけならば、ぶっちゃけた話マクロを組む必要性も薄いかと思います。
当然ながら質問者様が実はもっと複雑なマクロを組むことを考えておられ、今回のご質問は
そのほんの一部に過ぎない場合はご参考にして頂ければ幸いです。

余談ですが、他の方も書かれているとおり、On Error 処理はエラーをマスクしてしまうため、
マクロがうまく動いているように見えてしまいます。個人的にはあまり使わないようにしています。

たとえばこのケースで言うならば、

If WorksheetFunction.CountIf(prefRng.Resize(, 1), tmpStr) > 0 Then

といった具合に、tmpStrの有無をエラーではなくCountIfの戻り値で判断させるという方法もあります。
こうするとエラーが起きた箇所でマクロが「きちんと」とまりますので、どこに問題があるか等を確認
しながら進めることが可能です。

この回答へのお礼

ありがとうございます。できました。
大変助かりました。

お礼日時：2019/03/06 00:05

No.4 回答者： fujillin 回答日時： 2019/03/04 09:43

こんにちは

A列のコードで検索して、B列の県名、C列の所在地を表示したいものと解釈しました。
一方で、ご提示のコードにはその意図がまったく見られないのと、説明文にも曖昧なところがあるので、回答者に正しく伝わっていないように感じます。
（などといって、私のほうが誤解している可能性も十分にありますが…）

ワークシート関数は、VBAで利用する場合もシート内の関数とほぼ同様ですので、引数の意味等をきちんと理解して使わなければ（当然ながら）意図した結果は得られません。
https://docs.microsoft.com/ja-jp/office/vba/api/ …
https://support.office.com/ja-jp/article/lookup- …

ついでながら、ご提示のコードのエラー処理（のおつもりのもの）は、質問者様にとってコードの間違えの発見や修正をしにくくしているのではないかと想像します。
ON ERROR での処理を行う場合は、一応、意図通りに動作するコードを作成してから、追加なさったほうが宜しいように感じました。
また、ご提示のエラー処理内容の場合は、きちんと動作するコードを作成したあかつきには、ほとんど意味の無いものになってしまうことに気付くようになれると、さらなる理解につながるのではないかと想像します。


ちなみに、以下は、前回ご紹介しておいた、別法の対応法を修正した例です
https://oshiete.goo.ne.jp/qa/11002001.html

Sub Sample()
Const prefSh = "Sheet2"

With ThisWorkbook.Worksheets("Sheet1")
rwMax = .Cells(Rows.Count, 1).End(xlUp).Row
If rwMax < 2 Then Exit Sub

With Range(.Cells(2, 2), .Cells(rwMax, 3))
　.Formula = "=Vlookup($A2," & prefSh & "!$A$2:$C$48,COLUMN(B1),0)"
　.Value = .Value
End With
End With

End Sub

※ 前回の修正点も「修正したら動くのでOK」ではなく、「何のために修正しているのか」、「その結果何が起きているのか（なぜエラーがでないのか）」を理解するようにしておくことが大切かと思います。
（そうすることによって、他にもいろいろな修正方法があることがわかってくると思いますし、エラー発生時の修正に役立つようになることでしょう。）

No.3 回答者： tom04 回答日時： 2019/03/02 21:33

No.2です。

お示しのコードを拝見すると
Sheet1のA列を参照し、B列に表示するコートですよね？

もしかして、Sheet1のB列を参照し、C列に結果を表示したい！というコトでしょうか？
すなわちSheet2の検索列もB列で検索し、該当データがある場合はSheet1のC列にSheet2のC列データを表示する！

となると、前回のコードは消去して↓のコードに変更してみてください。

Sub Sample2()
　Dim i As Long, c As Range, wS As Worksheet
　　Set wS = Worksheets("Sheet2")
　　　With Worksheets("Sheet1")
　　　　For i = 2 To .Cells(Rows.Count, "B").End(xlUp).Row '//★//
　　　　　Set c = wS.Range("B:B").Find(what:=.Cells(i, "B"), LookIn:=xlValues, lookat:=xlWhole) '//★//
　　　　　　If Not c Is Nothing Then
　　　　　　　.Cells(i, "C") = wS.Cells(c.Row, "C") '//★//
　　　　　　Else
　　　　　　　.Cells(i, "C") = "ERROR" '//★//
　　　　　　End If
　　　　Next i
　　　End With
End Sub

※　「★」の行を変えています。m(_ _)m

No.2 回答者： tom04 回答日時： 2019/03/02 09:49

こんにちは！

お示しのコードはSheet1のB2セルに
=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),"ERROR")

としてフィルハンドルでダブルクリック！
これで大丈夫なのでは？

どうしてもVBAでやりたい！というのであれば、
自分であればFINDメソッドで対応します。

Sub Sample1()
　Dim i As Long, c As Range, wS As Worksheet
　　Set wS = Worksheets("Sheet2")
　　　With Worksheets("Sheet1")
　　　　For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
　　　　　Set c = wS.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
　　　　　　If Not c Is Nothing Then
　　　　　　　.Cells(i, "B") = wS.Cells(c.Row, "B")
　　　　　　Else
　　　　　　　.Cells(i, "B") = "ERROR"
　　　　　　End If
　　　　Next i
　　　End With
End Sub

こんな感じでしょうかね。

※　今回の「On Error Resume Next」は
単純にSheet2のA列に検索データがあるかどうか？だけの判断に使っているのだと思いますが
個人的には極力「On Error Resume Next」は使わないように努力しています。
万一エラーが発生した場合、どの部分がエラーなのか判らず、修正も難しくなります。m(_ _)m

この回答へのお礼

ありがとうございます。
ご教授いただいたコードで、Sheet１のC列に県庁所在地が表示されませんでした。
関数としてVlookupを考えていますが何が足りないのか苦戦しています。

お礼日時：2019/03/02 10:26

No.1 回答者： 銀鱗 回答日時： 2019/03/02 01:49

Set prefRng = Range(prefSh.Cells(2, 1), prefSh.Cells(48, 2))

これは何をしているのかな。

そして
　workSh.Cells(workTmpR, 2).Value = Application.vlookup(tmpStr, prefRng, 2, False)
は何をしているのかな。

よーーーーく考えてみよう。

まずはVLOOKUP関数がどんな事をやっているのかを「関数」として調べることを勧める。
（てか、エラーが出ても無視してVLOOKUP関数で同じことをやらせてみると良い）

この回答へのお礼

ありがとうございます。
関数としてVlookupを考えていますが苦戦しています。

お礼日時：2019/03/02 10:27