エクセルVBAでVLookupを使って値を転記する

質問

エクセル2003で商品の一覧表を作成しています。

Sheet1は商品一覧（左図）
Sheet2は価格表（右図）となっています。

マクロを使用して、Sheet1のB列に価格表のデータを転記させたいと考えています。

VLookupになるのかと思い、自分でいろいろとやってみたのですが、
どうしても動作せず、挫折してしまいました。

商品一覧の最後の行までいって、空白セルがくると止まるというのが、
難しくてできませんでした。

どうかお願いいたします。

回答No.1

tom04 · ベストアンサー

こんばんは！

わざわざVBAでやるメリットはないように思えますが・・・
Excel2003　というコトなので
Sheet1のB2セルに
=IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:B,2,0),"")
という数式を入れB2セルのフィルハンドルでダブルクリック！
これで完了だと思います。

どうしてもVBAでやりたい場合、一例です。

ワークシート関数をそのまま使用するのが一番簡単だと思います。

Sub Sample1()
Dim lastRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
With Range(.Cells(2, "B"), .Cells(lastRow, "B"))
.Formula = "=IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:B,2,False),"""")"
.Value = .Value
End With
End With
End Sub

別の方法としては、いかにもVBAらしくやると

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, "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")
End If
Next i
End With
End Sub

こんな感じでしょうか。

※　Sample2はループさせていますので、データ量が多い場合は
Sample1の方が速いと思います。m(_ _)m

お礼

早々の回答ありがとうございました。
早速試してみたいと思います。
２つも提案してくださって感謝いたします。

回答No.2

mar00

VBAにしなくとも普通に関数でよいのではないかと。

Sub Macro1()
Dim myRow As Long
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Range("B2"), Range("B" & myRow)).Formula = _
"=IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:B,2,FALSE),"""")"
End Sub

お礼

回答ありがとうございます。
思っていたことが出来るようになり、
大変満足しております。
お手数おかけいたしました。