エクセルVBA、リストA・Bを比較し、新規の値であった場合、新と入力したい

Question

前提・実現したいこと

エクセルVBAで、ワークシートws.allのA2～A10000の値が、
ワークシートws.listのA2～A1000にない新規の値であった場合、ワークシートws.allのB列に「新」と入力するマクロを作りたい

発生している問題・エラーメッセージ

リストにある場合、「すでに有り」と入力するマクロは作ることができたが、ない場合「新」とすることに苦労している

該当のソースコード

For i = 2 To 10000
        For j = 2 To 1000

            If ws_all.Cells(i, 1).Value = ws_list.Cells(j, 1).Value Then

            ws_all.Cells(i, 2) = "すでに有り"

            Exit For

            Else

            End If

        Next j

    End If

Next i

試したこと

リストが数十万行であり、ほとんどがリストBにあるためExit for でループを抜けることで
実行時間が短縮できるかと思っていますが、新と入力することが出来ず悩んでいます

Comments

[meg_]

・コードは「コードの挿入｣で記入してください。
・「リスト」とは配列のことですか？

[usaginote1020]

ご指摘ありがとうございます。質問を修正致しました。

[hatena19]

「ワークシートws.allのA4～D10000の値が」は
「ワークシートws.allのA4～A10000の値が」の間違いではないですか。

[usaginote1020]

失礼しました。ご指摘の通りです。修正いたします

[tatsu99]

それぞれのA2～A1000には、空白のセルがありますか。(歯抜けのセル)
それとも、全てデータが設定されてますか。

[usaginote1020]

いずれも空白セルはありません

Answer 1

もしも、VBAにこだわらないのであれば、ワークシートws.allのB2に以下の式を入れてください。
あとはA列のあるところまでB列にコピペしてください。

=IF(IFERROR(MATCH(A2,list!$A$2:$A$1000,0),"新規")="新規","新規","既存")

A列のデータが数の場合、10万件なら1秒程度で計算が終わりました。

Answer 2

以下のようにしてください。
ws_allは"Sheet1"
ws_listは"Sheet2"
にしています。あなたの環境にあわせてください。

Option Explicit

Public Sub 比較()
    Dim maxrow1 As Long
    Dim maxrow2 As Long
    Dim wrow As Long
    Dim ws_all As Worksheet
    Dim ws_list As Worksheet
    Dim dicT As Object
    Set ws_all = Worksheets("Sheet1")
    Set ws_list = Worksheets("Sheet2")
    maxrow1 = ws_all.Cells(Rows.Count, 1).End(xlUp).Row    'sheetの最大行取得
    maxrow2 = ws_list.Cells(Rows.Count, 1).End(xlUp).Row    'sheetの最大行取得
    Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義
    For wrow = 2 To maxrow2
        dicT(ws_list.Cells(wrow, 1).Value) = True
    Next
    For wrow = 2 To maxrow1
        If dicT.exists(ws_all.Cells(wrow, 1).Value) = True Then
            ws_all.Cells(wrow, 2).Value = "すでに有り"
        Else
            ws_all.Cells(wrow, 2).Value = "新"
        End If
    Next
End Sub

Answer 3

シート上のリスト(セル範囲)を比較するのを高速化したいということでしょうか。
セルを一つずつ参照するのは重い処理になるので、リスト(セル範囲)を配列に格納して配列で比較するといいでしょう。

Office TANAKA - VBA高速化テクニック[配列を使う]

コード例

Sub Sample()
    Dim ary_all()
    ary_all = WorkSheets("all").Range("A2:A10000").Value
    
    Dim ary_list()
    ary_list = WorkSheets("list").Range("A2:A1000").Value
    
    Dim ary_res() As String 
    ReDim ary_res(1 To UBound(ary_all), 1 To 1)

    Dim i As Long, j As Long, bNew As Boolean
    For i = 1 To UBound(ary_all)
        bNew = True
        For j = 1 To UBound(ary_list)
            If ary_all(i, 1) = ary_list(j, 1) Then
                bNew = False
                Exit For
            End If
        Next j
        If bNew Then ary_res(i, 1) = "新"
    Next i
    
     WorkSheets("all").Range("B2:B1000").Value = ary_res

End Sub

---

あるいは、関数で存在チェックしてもいいでしょう。

指定したデータがリスト内に存在するかどうかを確認する：Excel 一般｜即効テクニック｜Excel VBAを学ぶならmoug

VBAで関数を設定して、値に変換するという方法のコード例

Sub Sample2()
    With WorkSheets("all").Range("B2:B9")
        .Formula = "=IF(COUNTIF(list!$A$2:$A$8,all!A2),"""",""new"")"
        .Value = .Value
    End With
End Sub

Answer 4

なにか変数を用意しておき、最初に０を入れておきます
そして、その判定のところで、すでに有り、となるところでその変数に１を代入するようにして、
そこのExitForを抜いておきます

そうしておくと、そのfor文の実行が終わったときにその変数が０であった場合、新規、と判断できます