こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

マクロでVLookupが出来ません。

マクロでVLookupが出来ません。
A列からE列までデータがあるシート1のA,B,C列の値が全て一致する
シート2の行削除をしたいです。
2000行中500行残るはずのダミーで実験してますが全行削除されてしまいます。
Do Until Cells(Line, 6).Value
→6の部分を1
VLookup(Cells(Line, 6)
→6の部分を1にすれば
500行残ります。ですがこれでは検索値がA列のみの値です。
またそれぞれ1→2、1→3、1→4にしても同じく全行消えこんがらがってます。
シート1のA列のみ検索しているような動きです。どこを修正すればいいのでしょうか?

Sub 行削除()
'シート1→8月シート2→9月
Sheets("8月").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]"
Selection.AutoFill Destination:=Range("F2:F10000")
Sheets("9月").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]"
Selection.AutoFill Destination:=Range("F2:F10000")
'検索する対象値があるシート選択
Sheets("9月").Select
'そのシートの検索開始の行数を選択2行目。
Line = 2
'そのシートの検索値の列指定6=F列。セルF2の値が検索したい値。
'その値がなくなったら検索を終了させる.Value = ""を追加。
Do Until Cells(Line, 6).Value = ""
'エラーとなっても次に進む
On Error Resume Next
'検索結果を記入する列を指定。Line7=G列(※1)
'検索する値があるシートとその列を指定
'VLookup(Cells(Line, 6)の部分。6=F列
'検索されるシートと検索範囲を指定
'Worksheets("9月").Range("A2:F10000")→セルA2からセルF10000まで
'検索されたらその行のどの列の値を結果とするのか指定 1=A列
'検索方法指定0=FALSE完全一致。
Cells(Line, 7).Value = Application.WorksheetFunction.VLookup(Cells(Line, 6)_
.Value, Worksheets("8月").Range("A2:F10000"), 1, 0)
'VLOOKUP関数が終了又はエラーが発生したら止まる
On Error GoTo 0
'検索されなかったときの処理
'上記(※1)の部分Line7=G列に値がない
If Cells(Line, 7).Value = "" Then
'Line7=M列に無と表示
Cells(Line, 7).Value = "無"
End If
'2行目から開始なので次の行の値を検索値とする
Line = Line + 1
'検索する値がなくなるまで繰返す
Loop
'1行目が削除されるのを防止セルG1に無とセット
Sheets("9月").Select
Range("G1").Select
ActiveCell.FormulaR1C1 = "無"
'データの最終行の行番号を保持する変数
Dim RwMax As Long
'現在処理中の行番号を保持する変数
Dim Rw As Long
'対象となるシートを選択。
Worksheets("9月").Select
'データの最終行の行番号を取得。
'Count, 7=G列 においてデータが入っている一番下のセルの行番号
RwMax = Cells(Rows.Count, 7).End(xlUp).Row
'最終行から1行目まで繰返し処理。
'行の削除の為下から上へと処理。
For Rw = RwMax To 1 Step -1
'値が無ならそのまま
If Cells(Rw, 7).Value = "無" Then
Cells(Rw, 7).Value = "無"
'無でなかったら行削除
Else
Rows(Rw).Delete
End If
Next Rw
'シート2のF,G列を列削除
Sheets("9月").Select
Columns("F:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
End Sub

投稿日時 - 2010-09-30 18:49:12

QNo.6218713

困ってます

質問者が選んだベストアンサー

回答2、myRangeです。
質問の回答は、回答2ですが、今度はロジックについて。。
質問の処理をG列を使わないないで、かつ、Vlookupを使ってやるとすると。。。

'-------------------------------------------
Sub test()
 Dim Line As Long
 Dim LastRow As Long
 Dim myRange As Range
 Dim Flag
 
With Sheets("8月")
 Set myRange = .Range("F2:F" & .Cells(Rows.Count, "A").End(xlUp).Row)
 .Range("F2").FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]"
 .Range("F2").AutoFill Destination:=myRange
End With
 
Sheets("9月").Select
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Range("F2").FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]"
 Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)

On Error Resume Next

For Line = LastRow To 2 Step -1
 Flag = WorksheetFunction.VLookup(Cells(Line, 6).Value, myRange, 1, 0)
 If Err.Number = 0 Then
   Rows(Line).Delete xlUp
 Else
   Err.Clear
 End If
Next Line
 
End Sub
'----------------------------------------------

以上です。
 

投稿日時 - 2010-09-30 23:46:35

お礼

思ったとおりできました。

エクセルで関数式を入れる場合、

VLOOKUPを選択すると

(テーブルの左端列を検索し指定した列と同じ行にある値を
 返します。
 テーブルは昇順で並べ替えておく必要があります。)

とメッセージがでますが、
このマクロで行う場合も、
テーブルは昇順で並べ替えておく必要があるのでしょうか?

投稿日時 - 2010-10-01 13:41:07

ANo.3

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

またまた、myRangeです。 

>テーブルは昇順で並べ替えておく必要があるのでしょうか?

それについてはヘルプにちゃんと書いてあります。
以下ヘルプ抜粋。

-------------------------------------------------
VLOOKUP(検索値, 範囲, 列番号, ●検索の型●)

●検索の型●

TRUE(1)を指定するか省略すると検索値と完全に一致する値、またはその近似値が返されます
FALSE(0) を指定すると、検索値と完全に一致する値だけが検索されます

(重要)
検索の型に TRUE を指定するか省略する場合は、
範囲の左端の列にある値を、昇順に並べ替えておく▼必要あり▼

検索の型に FALSE を指定する場合は
範囲の左端の列にある値を並べ替える▼必要はない▼
----------------------------------------------------------

で、結論。
今回は、0(FALSEと同値)を指定しているので、並べる必要はない。

機会あるごとにヘルプを眺めることをお勧めします。
以上です。

投稿日時 - 2010-10-01 16:28:20

お礼

シート1に8,000行
シート2に8,500行のデータで試しました。

手操作でセルに式を入れてVLOOKUPで対象データを検出して
あいてる列に印を入力してその印でソートして
対象行を全部手削除した内容と完全一致しました。

ありがとうございました。これで手作業が不要です。

>機会あるごとにヘルプを眺めることをお勧めします。

了解しました。ありがとうございます。

投稿日時 - 2010-10-05 09:11:35

ANo.4

No.1です。

1行目が項目行だということなので、コードを↓のように訂正してみてください。
(前回のコード内で FOR~NEXT部分の 「1」を「2」にすれば大丈夫だと思います。)

尚、B列とC列が数値(品番?)みたいなので、前回の方法ですと
Sheet1の A列・B列・C列をつなげて一つにしたものでSheet2にSheet1を同じものがある行を削除するようにしていました。

これだとB・C列の桁数が違って、B・C列をつなげた場合、同じ結果になる場合があるかもしれません。

それを回避するためにA列はA列・B列はB列・C列はC列一つ一つが一致した場合にSheet2の行を削除するコードにしてみました。

Sub test()
Dim ws1, ws2 As Worksheet
Dim i, j As Long
Set ws1 = Worksheets("sheet1") '←Sheet名は適宜変更してください。
Set ws2 = Worksheets("sheet2") '←こちらのSheet名も適宜変更
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = ws2.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If ws1.Cells(i, 1) = ws2.Cells(j, 1) And ws1.Cells(i, 2) = ws2.Cells(j, 2) And _
ws1.Cells(i, 3) = ws2.Cells(j, 3) Then
ws2.Rows(j).Delete (xlUp)
End If
Next j
Next i
End Sub

投稿日時 - 2010-10-01 15:52:26

お礼

シート1に8,000行
シート2に8,500行のデータで試しました。

手操作でセルに式を入れてVLOOKUPで対象データを検出して
あいてる列に印を入力してその印でソートして
対象行を全部手削除した内容と一致しました。

ありがとうございました。
これで手作業が不要です。

投稿日時 - 2010-10-05 09:10:16

ANo.2

質問のタイトルに、VLOOKUPとあるので
VLOOKUPのみについていうと、構文にミスあり。

VLOOKUPは、指定範囲の1列目が検索対象になるので
指定範囲が「A2:F10000」だと、A列が検索されます。
質問ではF列を検索するわけですから、VLOOKUPの指定範囲は、

>Worksheets("8月").Range("●A2●:F10000")

ではなくて、次のように、”F列”の1列のみにします。

 Worksheets("8月").Range("▼F2▼:F10000")
 
----------------------------------------------------------
今回の質問とはかんけいないですが、複数行に分けるときは
分かりやすいところで改行した方が。。。
 
Cells(Line, 7).Value = _
 Application.WorksheetFunction.VLookup( _
 Cells(Line, 6).Value, Worksheets("8月").Range("F2:F10000"), 1, 0)

--------------------------------------------------
以上です。
 

投稿日時 - 2010-09-30 23:06:16

お礼

エクセルのセルに式を入れる場合で
VLOOKUPを選択すると

テーブルの左端列を検索し、指定した列と同じ行にある値を返します。
テーブルは昇順で並べ替えておく必要があります。

とメッセージがでました。

忘れてました。
マクロ初心者ですがRange(  )がよくでてくるので
これでいいと思ってしまいました。
ありがとうございました。

>複数行に分けるときは

すいません。ここで質問すると、意図しない部分で改行される為
それの対策で文を短くする為に適当にこうしました。
実際は1行です。

申し訳有りません。

投稿日時 - 2010-10-01 13:38:24

ANo.1

こんばんは!
お示しのコードを詳しく確認していません。
質問文の最初の数行を勝手に解釈しての投稿です。

Vlookupではありませんし、的外れだったら無視してください。

結局Sheet1のA~C列とSheet2のA~C列が一致すればSheet2のその行を削除する!という感じです。

Sub test()
Dim ws1, ws2 As Worksheet
Dim i, j As Long
Set ws1 = Worksheets("sheet1") '←Sheet名は適宜変更してください。
Set ws2 = Worksheets("sheet2") '←こちらのSheet名も適宜変更
For i = 1 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = ws2.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If ws1.Cells(i, 1) & ws1.Cells(i, 2) & ws1.Cells(i, 3) = _
ws2.Cells(j, 1) & ws2.Cells(j, 2) & ws2.Cells(j, 3) Then
ws2.Rows(j).Delete (xlUp)
End If
Next j
Next i
End Sub

投稿日時 - 2010-09-30 21:21:07

補足

ありがとうございます。
2000文字に達して詳細を書けませんでした。

シート1

 A   B   C D E
1 みかん 2222 6666 111 10001
2 りんご 2222 6666 112 10008
3 ぶどう 2222 6666 111 10013 
4 バナナ 1111 8888 112 10038
5 パイン 1111 8888 111 10054
6 いちご 3333 6666 113 10034
7 みかん 2222 7777 114 10027
8 すいか 4444 6666 112 10065
9 ぶどう 5555 6666 114 10063

シート2
シート1に対して重複したいない行が3行増加。
その3行の増加だけ抽出したい。

  A   B   C D E
1 みかん 2222 6666 111 10001
2 りんご 2222 6666 112 10008
3 みかん 2222 9999 111 10001
4 ぶどう 2222 6666 111 10013 
5 バナナ 1111 8888 112 10038
6 パイン 1111 8888 111 10054
7 キウイ 1111 8888 112 10098
8 トマト 1111 7777 118 10100
9 いちご 3333 6666 333 10034
10 みかん 2222 7777 114 10027
11 すいか 4444 6666 222 10065
12 ぶどう 5555 6666 114 10063

みかんが3行ありますがA,B,C列がシート1と一致するのは
1行目と10行目だけなので3行目は残ります。
9行目のいちごと11行目のすいかはシート1に対して
D列の値が相違しますがA,B,C列は同じ値なので重複データとみなします。

↓行削除後

  A   B   C D E
3 みかん 2222 9999 111 10001
7 キウイ 1111 8888 112 10098
8 トマト 1111 7777 118 10100

投稿日時 - 2010-10-01 09:02:04

お礼

説明不足で申し訳ありません。

思ったように動作しました。

ただシート1、シート2共に
1行目には項目が入っています。
よって1行目は削除はまずいのですが、
1行目も削除されてしまいました。
申し訳ありません。

投稿日時 - 2010-10-01 13:29:51

あなたにオススメの質問