VBAでVLOOKUP関数のように動作させたい

質問

VBA初心者の者です。

勉強として、VBAでVLOOKUPのよう作動させてみようと思い、以下のようにコードを書きました。

Sub 試験()

Dim i As Integer,　mx As Integer

mx = 2

For i = 2 To 10

If Worksheets("Sheet2").Cells(mx, 1) = Worksheets("Sheet1").Cells(i, 1) Then

Worksheets("Sheet2").Cells(mx, 2).Value = Worksheets("Sheet1").Cells(i, 2).Value

mx = mx + 1

Else

Worksheets("Sheet2").Cells(mx, 2).Value = 0

mx = mx + 1

End If

Next i

End Sub

※sheet1が参照範囲、sheet2が検索値です。

これを実行させたところ問題にきづきました。
sheet1の参照範囲がループによって1ずつ増加していく点です。
（ループ一周目の参照範囲　A2～A10）
（ループ二週目の参照範囲　A3～A10）
上記のように参照範囲を1ずつ増加させず、常に　sheet1　のA2からA10　まで
参照させたいのですが、どのようにすればよいのでしょうか？

ワークシート関数は使わない方向で考えています。

アドバイスお願い致します。

回答No.3

hallo-2007 · ベストアンサー

Ｎｏ1です。とりあえずＶＢＡの勉強という事で
＞ご回答下さった二重ループだと、どのような動きになるのでしょうか？
mx = 2
For i = 2 To 10
Worksheets("Sheet2").Cells(mx, 2).Value = 0
If Worksheets("Sheet2").Cells(mx, 1) = Worksheets("Sheet1").Cells(i, 1) Then
Worksheets("Sheet2").Cells(mx, 2).Value = Worksheets("Sheet1").Cells(i, 2).Value
End If
Next i

だった場合
Ｓｈｅｅｔ２のＢ２セルをとりあえず　０にする。
Ｓｈｅｅｔ2の2行目の値がSheet1の１～１０行目と同じときに、そのＢ列の値がＳｈｅｅｔ２のＢ２に入ります
同じ値がなければ、そのまま　０が残ります。

次に
mx = 3　の時を考えます。
Ｓｈｅｅｔ２のＢ3セルをとりあえず　０にする。
Ｓｈｅｅｔ2の3行目の値がSheet1の１～１０行目と同じときに、そのＢ列の値がＳｈｅｅｔ２のＢ3に入ります
同じ値がなければ、そのまま　０が残ります。

これを、mxが１０まで繰り返してくれるように
（この動作を、Ｓｈｅｅｔ２の2行目から10行目まで繰り返すために）
For mx = 2 To 10

For i = 2 To 10
ここで、Ｓｈｅｅｔ１の2行目から10行目までの口返し
Next i

Next mx

つまり　Ｓｈｅｅｔ１の２～１０までの8回の繰り返しを
Ｓｈｅｅｔ２の２～１０まで8回繰り返し。
途中のＩＦ文は　8*8の64回実行されます。

お礼

なるほど！
二重ループなるものがおそらく理解できたと思います。

一つ一つの動作を丁寧に教えていただいてありがとうございます。
For～Nextの理解が乏しいが故に勘違いに気づきました。

教えていただいたコードを理解して上で活用させていただきたいと思います。
ただ、実際の在庫管理では10000アイテム以上あるので、かなり処理に時間かかりそうです…
また、いい方法模索してみます。

回答No.2

Siegrune

>sheet1の参照範囲がループによって1ずつ増加していく点です。
本当にそうなっていますか？（たまたまなのでは？）
例えば、
sheet2の1行目は、sheet1の5行目にヒットする。
sheet2の2行目は、sheet1の3行目にヒットする。
というデータで試してみてください。
2行目は見つからないはずです。

なぜかって？
If Worksheets("Sheet2").Cells(mx, 1) = Worksheets("Sheet1").Cells(i, 1) Then
Worksheets("Sheet2").Cells(mx, 2).Value = Worksheets("Sheet1").Cells(i, 2).Value
mx = mx + 1
Else
Worksheets("Sheet2").Cells(mx, 2).Value = 0
mx = mx + 1
End If
としているので、見つかったら、sheet2の次の行を、sheet1の見つかった行から
探しに行くようにしているからです。

sheet2の1行目は、sheet1の10行目にヒットする。
sheet2の2行目は、sheet1の1行目にヒットする。
sheet2の3行目は、sheet1の2行目にヒットする。
・・・
で試してみてください。結果は、1行目以外はみんな0のはずです。

sheet2の1行目から、ｎ行目に対して、毎行、
sheet1の2行目から10行目をチェックするということをしたいと思われるので、
sheet1のチェックをしている行iと、sheet2の対象行mxはそれぞれ、
for～nextで繰り返す必要があるということで、ANo.1の方が回答を書かれているはずです。

他にdo loopでする方法やifで条件が成立したらexit forで抜けたほうが早いなど
もろもろあるのですが、まあ今回は割愛させていただいて。

で、そもそもの
>sheet3で統合した商品コードでVLOOKUPをかけた際、前期に在庫があり、当期に在庫がない場合、>sheet3の当期数量欄に参照先エラーが返されてしまいます。
ですが、こういうときは、
if(isna(vlookup(A10,前期!A:B,2,false))=true,0,vlookup(A10,前期!A:B,2,false))
+
if(isna(vlookup(A10,当期!A:B,2,false))=true,0,vlookup(A10,当期!A:B,2,false))
という形の式を使います。
isna(・・・)は、・・・のvlookup関数が見つからなくて#N/Aを返したときtrueになるので、
そのときは0を代入するという意図です。

お礼

おっしゃる通りのような現象になってしまい、困っていました。
日本語力不足ですみません。

丁寧に説明していただきありがとうございます。

回答No.1

hallo-2007

どういう結果がご希望なのかわかりませんが、ひょっとして
Sub 試験()

Dim i As Integer,　mx As Integer

For mx = 2 To 10

For i = 2 To 10

Worksheets("Sheet2").Cells(mx, 2).Value = 0

If Worksheets("Sheet2").Cells(mx, 1) = Worksheets("Sheet1").Cells(i, 1) Then

Worksheets("Sheet2").Cells(mx, 2).Value = Worksheets("Sheet1").Cells(i, 2).Value

End If

Next i

Next mx

End Sub
といったように、二重のループが必要かな？
Elseの時に
Worksheets("Sheet2").Cells(mx, 2).Value = 0
も意味が分かりませんでしたので、移動させました。

補足

ご回答ありがとうございます。
説明不足があってすみません。

二期間の商品在庫変動で活用できればなと思い検討中です。
本来はsheet1に前期在庫、sheet2に当期在庫、sheet3に前期と当期の商品コードを統合したものを用意し、VLOOKUPでSheet3に各期の在庫数をひっぱっています。

在庫数がゼロの場合は前期在庫・当期在庫にはその商品コードが生じません。

そのため、sheet3で統合した商品コードでVLOOKUPをかけた際、前期に在庫があり、当期に在庫がない場合、sheet3の当期数量欄に参照先エラーが返されてしまいます。
つまり、VBAを使った際にもsheet3にある商品コードがsheet1若しくはsheet2にないケースがあります。
その際に０を入力させるために以下のコードを入力しています。

Worksheets("Sheet2").Cells(mx, 2).Value = 0

ご回答下さった二重ループだと、どのような動きになるのでしょうか？
条件分岐が一周すると 変数i は　１増加してしまいそうなのですが…
変数iは何度ループされても 2 to 10 を常に検索するようにしたいと考えています。
（ループ使うべきではないのかもしれませんが、対処方法がわかりません。）
変数mx はループごとに１ずつ増加することで問題はありません。

説明下手ですみません。