エクセルで完全に一致した列のみ抽出する

質問

先週と今週のデータ変更を比較し、変更があった場合、先週と今週のデータ両方とも抽出する、というVBAを書いています。
　手順としては、
１．sheet"先週"とsheet"今週"のセルをひとつひとつ比較して、違った場合に色づけをする。
２．sheet"差分"に先週、今週両方のデータをコピペする。
３．IDが２ずつある状態で、IDごとにオートフィルタをかけ、B2とB3,C2とC3、、以下Z２とZ3まで上下で比較し、完全に一致だったら、AA列に"重複"と入力する。
　最後の列まで行ったら、再度オートフィルタをかけなおし、AA列の"重複"のみ削除する。
というものです。
３の部分は以下のように書きました。
With Worksheets("差分")
.Range("A:Z").AutoFilter Field:=1, Criteria1:="1"
If Range("B2").value = Range("B3") Then
.Range("AA2").Value ="重複"
.Range("AA3").Value ="重複"
Else
.Range("AA2").Value =xlNone
.Range("AA3").Value =xlNone
.AutoFilterMode = False
.Range("A:Z").AutoFilter Field:=27, Criteria1:="重複"
.autofilter.range.offset(1).delete shift:=xlshiftup
　　End With

上記の最初の抽出条件を１～１００（増える予定）、
B2,B3,AA2,AA３を抽出条件によって変更したいのですが、
どうしたらいいのでしょうか。
ためしに
"B２"と"B3"を、Range(cells(2,2)),　Range(cells(3,2))にしたら変更しやすいかと思いましたが、これだと1004エラーとなってしまいました。
最初の抽出条件は　抽出条件をiとし,For i = 1 to 100　という風に書いていますが、比較セルへの変更方法がわからずとまっています。

いい方法を教えてください。
ちなみにexcel　2000です。

回答No.1

kmetu · ベストアンサー

条件がiというのが良くわかりませんが

> B2,B3,AA2,AA３を抽出条件によって変更したいのですが、
> どうしたらいいのでしょうか。

それぞれ変数にしたらいかがですか

Range(変数(i))とかの感じで。

> "B２"と"B3"を、Range(cells(2,2)),　Range(cells(3,2))にしたら変更しやすいかと

単純にCells（2,2）じゃだめなんですか？

補足

IDひとつひとつでオートフィルタをかけるため、仮にiとして、Criteria1:="i"で順番に検索していくイメージでした。
Cells(2,2)だと値をとってくることすらできないので、せめて値の取れた上記を掲載しました。

1の時は、比較対照がB2,B3ですが、2だとB4,B5、100だとB210、B211となります。
これを指示する？式が思いつきません。。。。

回答No.6

kmetu

> ひょっとして、IDでフィルタをかけなくても、B～M列の2行目と3行目、4行目と5行目を比較できてたりしますか、、、、、？

たとえばIDでソートすれば(必ず2個ずつあるのなら)同じIDがそれぞれ上下2行にID順に並びますので、それをそれぞれ比較すれば必要回数(IDのセル(A列でしょうか)のデータのある行分/2)だけループするだけですみます。

前回勘違いしてましたが(A列に同じIDが2個ずつあるというのを忘れてました)

For x = 1 To Range("A" & Rows.Count).End(xlUp).Row / 2

にしてください。

それと

If Cells（i * 2,j）.value <> Cells（i * 2 + 1,j） Then

この比較をして、TRUEになった場合、上下の行は完全に一致しないことがその時点で分かりますからそのセル以降の比較は必要なくなりますのでループから Exit For したほうがいいでしょう。

お礼

ありがとうございました。
Exit Forにしたら、なぜか最後の列しか判定しなくなったので、本来のままにし、オートフィルタをはずしたら格段に早くなりました。
いろいろとありがとうございました。

回答No.5

kmetu

For i=1 to 100

なぜ100回ループさせるのかが良くわかりません。

If Cells（i * 2,j）.value <> Cells（i * 2 + 1,j） Then

2と3行　～　100と101行まで　ID数分だけチェックしてるわけですが
IDでフィルターをかけてる意味がないような気がしますが…

補足

同じIDでのみ重複を確認するために、まずIDでフィルターをかけ、B列ならB列の上下（IDは2ずつなので）で重複かどうかを確認するため、なのですが、、、、
ひょっとして、IDでフィルタをかけなくても、B～M列の2行目と3行目、4行目と5行目を比較できてたりしますか、、、、、？

回答No.4

kmetu

> この場合は、B2,B3 を比較したら、C2,C3と比較を進めていくには、仮にjとして、For B To M でいいのでしょうか？

For j=2 to 13
For i=1 to 100

Cells（i * 2,j）
Cells（i * 2+1,j）

Next i
Next j

になります。

> また、IDの最後の数字までを求めるにはどうしたらいいのでしょう？

1行目からデータの入っている最終行までなら

For x=1 to Range("A" & Rows.Count).End(xlUp).Row

next

となります。

お礼

ありがとうございます。
結局そのままでは、最後のセルが重複ならすべて上書きされて、結局重複になってしまうため、以下のようにしました。
For x=1 to Range("A" & Rows.Count).End(xlUp).Row
For j=2 to 13
For i=1 to 100
With Worksheets("差分")
.Range("A:Z").AutoFilter Field:=1, Criteria1:=x
If Cells（i * 2,j）.value <> Cells（i * 2 + 1,j） Then
.Range(i * 2,28).Value ="*"
.Range(i * 2 + 1,28).Value ="*"
Else
.Range(i * 2,27).Value ="重複"
.Range(i * 2 + 1,27).Value ="重複"
.AutoFilterMode = False
.Range("A:Z").AutoFilter Field:=27, Criteria1:="重複"
.Range("A:Z").AutoFilter Field:=27, Criteria1:="="
.autofilter.range.offset(1).delete shift:=xlshiftup
　　End With

これで一応の処理はできるのですが、ものすごく重いです。。。
お礼ポイントしかできない身で申し訳ないのですが、軽くする方法がありましたら教えてください。

回答No.3

kmetu

Range("B" & i * 2)
Range("B" & i * 2 + 1)

です。

回答No.2

kmetu

> 1の時は、比較対照がB2,B3ですが、2だとB4,B5、100だとB210、B211となります。

100だと200と201ではないのでしょうか？

B&i*2
B&i*2+1

で求められます。

お礼

ありがとうございます。

あまりにも簡単な答えを出せなかった自分にがっかりです。
質問に書き忘れたのですが、実はBからMまで検索を繰り返します。
この場合は、B2,B3 を比較したら、C2,C3と比較を進めていくには、仮にjとして、For B To M でいいのでしょうか？
また、IDの最後の数字までを求めるにはどうしたらいいのでしょう？
最後の行を求めるのは、
.Range("A" & Rows.Count).End(xlUp).Value
でいいかとおもいますが、これを-1/2というような形で、iを求める？？