エクセルでデータの比較をしたいです。お教え頂けないでしょうか

質問

エクセルでデータの比較をしたいです。お教え頂けないでしょうか

エクセルで２つのシートにある同一の商品コードと
在庫数を比較するマクロを作成中です。

シート1のA列にある商品コードとB列にある在庫数を取得し
シート2のA列にある商品コードから同じ商品コードを探します。

同一の商品コードがあった場合に在庫数を比較して
その数が減少していなければC列に次の処理を加える。　

商品コードが合致した後は 処理を抜けて次の商品コードを比較させたいのですが
下行にある商品コードを探し続けてしまいます。（データの総当りとなる）

つきましては どの様に記述すれば良いのでしょうか
お教え頂けます様 よろしくお願い致します。

＊＊＊＊＊＊＊＊＊＊＊

Sub check1()

Dim kz1 As long 'シート1データ数
Dim kz2 As long 'シート2データ数
Dim st1 As String 'シート名
Dim dt1 As Variant '商品コード
Dim dt2 As Variant '在庫数

Sheets("sheet1").Select
st1 = ActiveSheet.Name
kz1 = Range("a65536").End(xlUp).Row
Range("a1").Select
ActiveCell.Offset(1, 0).Select

For a = 0 To kz1 - 2

Sheets(st1).Select
dt1 = ActiveCell.Value '商品コード
dt2 = ActiveCell.Offset(0, 1).Value '在庫数

Sheets("sheet2").Select
kz2 = Range("a65536").End(xlUp).Row
Range("a1").Select
ActiveCell.Offset(1, 0).Select

For b = 0 To kz2 - 1

'同一商品コードを検索
if activecell.value = dt1

'在庫数を比較
if activecell.value >= dt2
'在庫数が同じ もしくは増加していた場合に処理
'次の処理を追加
endif

else
ActiveCell.Offset(1, 0).Select
endif

Next b
Sheets(st1).Select
Next a

end sub

回答No.3

nattocurry · ベストアンサー

コードを提示する場合は、書き写すと書き写し間違いの可能性があるので、コピペするようにしましょう。

if activecell.value = dt1
これなんて、文法的にエラーですよね。

セルの値を調べたり、セルに値を入力したりする場合は、わざわざシートやセルをSelectする必要はありません。

たとえば、Sheet1のA1に、Sheet2のB2の値を入力したかったら、
Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet2").Range("B2").Value
だけで良いです。

Dim st1 As String 'シート名
Sheets("sheet1").Select
st1 = ActiveSheet.Name
Sheets(st1).Select

このように、シート名用の文字列変数を使うのも一つの手ですが、それよりも、st1をワークシート変数として使うほうが、一般的だし、便利です。
st1をワークシート変数として使うと、上記のコードは、下記のようになります。

Dim st1 As Worksheet
Set st1 = Sheets("sheet1")
st1.Select

そして、Sheet1のA1は、

st1.Range("A1")

と書くことが出来ます。

Forループを抜けたいときは、Exit For です。

セルをいちいち選択して、ActiveCellを変数代わりに使っているから、ややこしいことになっていると思われます。
セルも変数を使いましょう。

Forループのカウンターとして使っているaもbも、ループの中で使用していないので、ループはDo Whileループ、もしくはDo Untilループを使うのも良いでしょう。

これらを踏まえて、コードを書き直すと、以下のようになります。

Sub check2()
Dim st1 As Worksheet
Dim st2 As Worksheet
Dim cl1 As Range
Dim cl2 As Range

Set st1 = Sheets("sheet1")
Set st2 = Sheets("sheet2")

Set cl1 = st1.Range("A2")
Do Until cl1.Row > cl1.Range("a65536").End(xlUp).Row
Set cl2 = st2.Range("A2")
Do Until cl2.Row > cl2.Range("a65536").End(xlUp).Row
'同一商品コードを検索
If cl2.Value = cl1.Value Then
'在庫数を比較
If cl2.Offset(, 1).Value >= cl1.Offset(, 1).Value Then
'在庫数が同じ もしくは増加していた場合に処理
'次の処理を追加
Exit Do
End If
End If
Set cl2 = cl2.Offset(1)
Next b
Set cl1 = cl1.Offset(1)
Next a
End Sub

お礼

nattocurryさん

はじめまして ご回答して頂きありがとうございます

お伺いしたい内容には不必要な処理が多かった為に
手入力にて修正をしたところ文法誤りをしていた様です。
大変失礼致しました。

大変判り易いご説明をして頂きまして
希望していた事が進められそうです。
皆さん お時間を割いて頂きまして心より深く感謝申し上げます。

回答No.2

imogasi

雑感
下記参考にしてみて。
質問者はまだプログラムロジックというものが色々あるという経験を積んでないようだ。
マッチングという手法もあるが。
質問にはそのロジックの説明が無い。意識が薄い状態では。それによってコードが短くなったりする。
マクロの記録で出てくるSelectは、VBAが良くわかって慣れれば、ほとんど使わなくて良い。
特にセルのSelectはね。
それに両シートを扱うときのSet　Sh1=worksheets("Sheet1")のような書き方も使ってない。
WEBで照会すること。
また両シートで、同じ商品コードが複数行現れるケースかも認識してますか。書いてないのであいまいのままでは。これが処理に大きく影響する場合が多いよ。
ーー
検索するにはどういうロジックがあるのか勉強のこと。
全行総なめ
Findメソッド
両データをソートして検索不要に持ち込む
フィルタ利用
SQLなど
１つしか該当ないならVLOOKUPやMATCHのVBA関数の利用
など勉強してみて。

お礼

imogasiさん

はじめまして ご回答して頂きありがとうございます
ご指摘の通り VBというものをよく理解していない中で
本マクロを作成しております。

また 同じコードが複数ある場合など想定が足りておらず
おはずかしい限りでございます。

記述の簡略化など大変参考になりました。
お伺いしたように 理解を深められるように時間を設ける事に致します。

回答No.1

myRange

コード合致処理の後はFor～Nextから抜けるために、Exit For を入れてやります。

'---------------------------------------------------
Sub test()
　Dim R1 As Long
　Dim R2 As Long
　Dim Sht1 As Worksheet
　Dim Sht2 As Worksheet

　Set Sht1 = Worksheets("Sheet1")
　Set Sht2 = Worksheets("Sheet2")

　For R1 = 2 To Sht1.Cells(Rows.Count, "A").End(xlUp).Row
　　　For R2 = 2 To Sht2.Cells(Rows.Count, "B").End(xlUp).Row
　　　　　If Sht1.Cells(R1, "A").Value = Sht2.Cells(R2, "A").Value Then
　　　　　　　If Sht1.Cells(R1, "B").Value > Sht2.Cells(R2, "B").Value Then
　　　　　　　　　　'▼在庫減少の処理
　　　　　　　Else
　　　　　　　　　　'▼在庫同じ＆増加の処理
　　　　　　　End If

　　　　　　　Exit For '●コード合致したのでForを抜けて次のコードへ

　　　　　End If
　　　Next R2
　Next R1

End Sub
'---------------------------------------------------

今回のような場合は、シート等をいちいちSelectする必要ありません。

また、データが多い場合は、コード検索にはFindメソッドを使いますが
質問者の場合はまだFor～Nextを理解してからでいいでしょう。
以上です。

お礼

myRangeさん

はじめまして ご回答して頂きありがとうございます
早速 使わせて頂きたいと思います。

これまで必要以上の無駄な記述をしていた様です
大変参考になりました。　ありがとうございます。