VBAでVlookup関数を組もうとしていますがエラーが出ます。VBAに詳しい方、教えてください

VBAでvlookup関数を下のように組みましたが、(1)でエラーが出ます。VBAに詳しい方、教えてください。
Sub VLLOKUPによる表の検索4()
Dim mykensakuchi
Dim mykensakuhan
Dim gyo As Integer
(1) mykensakuchi = Worksheets("sheet1").Range("a" & gyo).Value
mykensakuhan = Worksheets("sheet2").Range("b2:e9")
saikagyo = Worksheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row
gyo = 2
For gyo = saikagyo To 1 Step -1
With Application.WorksheetFunction
Range("b:gyo").Value = .VLookup(mykensakuchi, mykensakuhan, 2, False)
End With
Next
End Sub

No.1 回答者： hallo-2007 回答日時： 2009/05/24 18:15

デバックモードで

(1) mykensakuchi = Worksheets("sheet1").Range("a" & gyo).Value
が黄色くなっていると思いますが
gyoのところにマウスを移動すると何が入っているでしょうか？
Range("b:gyo").Valueのところもおかしいと思います。
Range("b:" & gyo).Value

この回答への補足

さっそく回答いただきありがとうございました。指摘いただいた部分をなおし、以下のようなコードにしたらエラーは出なくなり、キーがマッチする場合は正しい結果が返ってくるようになりました。しかし、キーがマッチしない場合は、そこで処理がとまってしまい、（２）の箇所でエラーが出ます。VLOOKUP関数を使う上で、何かコードの記述がもれているのでしょうか？

Sub VLLOKUPによる表の検索4()
Dim mykensakuchi
Dim mykensakuhan
Dim gyo As Integer
gyo = 2
mykensakuhan = Worksheets("sheet2").Range("b2:e9")
saikagyo = Worksheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row

For gyo = 2 To saikagyo Step 1
mykensakuchi = Worksheets("sheet1").Range("a" & gyo).Value
With Application.WorksheetFunction
(2) Range("b" & gyo).Value = .VLookup(mykensakuchi, mykensakuhan, 2, False)
End With
Next
End Sub

補足日時：2009/05/24 20:21

No.2 ベストアンサー 回答者： Wendy02 回答日時： 2009/05/24 20:53

こんばんは。

現在書いたコードの難易度としては、中の上から、上級です。入門レベルではVLookupのエラーを、'On Error トラップ'を捉えるのは、無理です。テキストにも以下のような使い方は出ていないはずです。

できれば、Findメソッドなどを使ったほうが分かりやすいです。（ヘルプに参考コードが出ています)

それに、コードが間違いやすい書き方です。変数は、もう少し、分かりやすく書いたほうがよいです。今回はそのままにしましたが、3語程度までにしてほうがよいです。また、Sub プロシージャ名は、特に理由はありませんが、英語かローマ字を使ってください。データ型は、難しければ、つけなくてもよいです。また、ワークシートの関数は、一般的には、Variant にしてください。

myKensakuChi　こういう書き方を、キャメル型といって、入力を間違えないようにするためです。（というか、そのまま使うためには、キャメル型にせざるを得ませんでした。)

'----------------------------------------------------

Sub Search_by_VLOOKUP_4()
　　Dim myKensakuChi As Variant
　　Dim myKensakuHan As Range
　　Dim gyo As Long
　　Dim saikaGyo　 As Long
　　Dim ret As Variant
　　gyo = 2
　　saikaGyo = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
　　Set myKensakuHan = Worksheets("Sheet2").Range("B2:E9")
　　
　　On Error Resume Next
　　'今回は、下からする必要はあまりないはずです。
　　For gyo = saikaGyo To 1 Step -1
　　　　ret = ""
　　　　myKensakuChi = Worksheets("Sheet1").Range("A" & gyo).Value
　　　　ret = WorksheetFunction.VLookup(myKensakuChi, myKensakuHan, 2, False)
　　　　If ret <> "" Then
　　　　　　Worksheets("Sheet1").Range("B" & gyo).Value = ret
　　　　End If
　　Next
　　On Error GoTo 0
End Sub

この回答へのお礼

Wendy02 さん
ご丁寧にコードまで記述していただき、ありがとうございました。
いただいたコードを実行したら思っているとおりに動きました。
また、コード記述の注意点等についてもご指導いただきありがとうございます。
まだまだ初心者ですが、これから勉強していきます。
ありがとうございました。

お礼日時：2009/05/24 21:20