配列に入力した値を順にVLOOKUPして指定セルに結果を表示したい

投稿者 : まさ     投稿日時 : 2023/09/11(Mon) 13:31:34     OS : Windows 10     EXCEL : Excel 2016

---

Sheet1のZ列に；で区切られて入力されている複数アドレスを分割し、その後分割したアドレスを順にVLOOKUPの検索値に入れ、値が一致した場合、Sheet2のW列2行目に検索結果を表示を最終行まで繰り返すができないかと考えています。

「Sheet1のZ列に；で区切られて入力されている複数アドレスを分割」部分については、
次のVBAでアドレスを分割することはできました。

Sub アドレス分割() 
 
    Dim ws01, ws02 As Worksheet
    Dim i, L, lRow As Long
    Dim Buf As Variant
    
    Set ws01 = Worksheets("Sheet1")
    Set ws02 = Worksheets("Sheet2")

　　'ワークシート「Sheet1」A列の最終行（空白なく値が入力されている）を取得しています  
    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row     

　　'ワークシート「Sheet2」のアドレス入力予定欄の値をクリアしています   
    ws02.Range("AR2:GZ500").ClearContents
    
    For i = 2 To lRow   '１行目からA列の最終行まで繰り返えさせています
    
        Buf = Split(ws01.Cells(i, 26), ";")     'Split関数を使ってカンマ区切りのZ列の文字列を分割して配列に登録しています
        
        For L = 0 To UBound(Buf)  '文字列を分割した配列要素分繰り返しています
            ws02.Cells(i, L + 44) = Buf(L)       '配列に分割した文字列を「Sheet2」のAR列セルから順に転記させています
        Next L
    
    Next i
 
End Sub

さてここからが本題なのですが、
やりたいことの後半部分の「分割したアドレスを順にVLOOKUPの検索値に入れ、値が一致した場合、Sheet2のW列2行目に検索結果を表示」を最終行まで繰り返すができればと考えています。

もともとW列2行目のセルには関数で次のように書いていたのですが、
「=IF(V2="","",IFERROR(VLOOKUP(検索値,参照範囲,列目,FALSE),""))」
この検索値に、分割したアドレスを順に入れて実行するようなマクロをつくることはできないでしょうか。

（イメージしている流れ）

①AR列2行目のアドレスを検索→②ヒットすれば結果をW列2行目に入力→③AR列3行目のアドレスを検索
↓
②'ヒットしなければAS列2行目のアドレスを検索→②→③
↓
②''ヒットしなければAT列2行目のアドレスを検索→②→③
↓
・
・
・
アドレスが入力されている列数分繰返す


③→④ヒットすれば結果をW列3行目に入力→⑤AR列4行目のアドレスを検索
↓
④'ヒットしなければAS列3行目のアドレスを検索→④→⑤
↓
・
・
・
といった感じで、AR列の最終行まで繰り返し実行できればと思っています。

どうぞよろしくお願いいたします。

投稿者 : さんこう     投稿日時 : 2023/09/11(Mon) 13:58:49

---

＞「分割したアドレスを順にVLOOKUPの検索値に入れ、値が一致した場合、Sheet2のW列2行目に検索結果を表示」を最終行まで繰り返すができればと考えています。

ご提示のコードで、

「配列に分割した文字列を「Sheet2」のAR列セルから順に転記させ」る代わりに、

「VLOOKUPの検索値に入れ、値が一致した場合、Sheet2のW列2行目に検索結果を表示」するようにすれば、

「最終行まで繰り返すができ」るのではないでしょうか。

投稿者 : まさ     投稿日時 : 2023/09/11(Mon) 14:29:08

---

■[返信 1] さんこうさん（2023-09-11 13:58:49）の記事
＞ ＞「分割したアドレスを順にVLOOKUPの検索値に入れ、値が一致した場合、Sheet2のW列2行目に検索結果を表示」を最終行まで繰り返すができればと考えています。
＞
＞ ご提示のコードで、
＞
＞ 「配列に分割した文字列を「Sheet2」のAR列セルから順に転記させ」る代わりに、
＞
＞ 「VLOOKUPの検索値に入れ、値が一致した場合、Sheet2のW列2行目に検索結果を表示」するようにすれば、
＞
＞ 「最終行まで繰り返すができ」るのではないでしょうか。
＞

さんこうさん回答ありがとうございます。
マクロ初学者であることを書き忘れていました。
また、検索参照範囲は別ブックになるのですが、
こんな感じになるということでしょうか。

しかし、Nextに対応するForがありませんというエラーが出てしまいました。
For Lの中身を変えたのと、参照先のBookを開くを入れたのですが、原因がわかりましたらご教授お願い致します。

Sub アドレス検索()

Workbooks.Open "Book1.xlsx"
 
    Dim ws01, ws02 As Worksheet
    Dim i, L, lRow As Long
    Dim Buf As Variant
    
    Set ws01 = Worksheets("Sheet1")
    Set ws02 = Worksheets("Sheet2")
    
    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row     'ワークシート「データ」A列の最終行を取得
    
    ws02.Range("AR2:GZ500").ClearContents
    
    
    For i = 2 To lRow   '１行目からA列の最終行まで繰り返します。
    
        Buf = Split(ws01.Cells(i, 26), ";")     'Split関数を使ってカンマ区切りの文字列を分割して配列に登録
        
        For L = 0 To UBound(Buf)  '文字列を分割した配列要素分繰り返す
             
             With Workbooks("Book1.xlsx").Worksheets("Book1Sheet")
             ws02.Range("AR" & i) = WorksheetFunction.VLookup(Buf(L), .Range("B:O"), 14, False)　'配列の値を検索値に入れVLOOKUP
        
        Next L
    
    Next i

 
End Sub

投稿者 : さんこう     投稿日時 : 2023/09/11(Mon) 14:36:51

---

＞Nextに対応するForがありませんというエラーが出てしまいました。

このメッセージは、本来なら「Withに対応するEnd Withがありません」となるべきものです。

（Microsoftの手抜きでしょうか）

投稿者 : higeru     投稿日時 : 2023/09/11(Mon) 15:38:14

---

■[返信 2] まささん（2023-09-11 14:29:08）の記事
＞ With Workbooks("Book1.xlsx").Worksheets("Book1Sheet")

　ここしか見てなくて、まったく本質的でないツッコミですみません。"End With" がないというのはすでに指摘されてますが、なにゆえここで With ？
　ここまでの書き方との整合性というか一貫性からしてここは

　　Dim ws03 as Worksheet: Set ws03 = Worksheets("Book1Sheet")

とかして .Range("B:O") → ws03.Range("B:O") としたいところ。

投稿者 : higeru     投稿日時 : 2023/09/11(Mon) 16:06:07

---

■[返信 3] さんこうさん（2023-09-11 14:36:51）の記事
＞ ＞Nextに対応するForがありませんというエラーが出てしまいました。
＞
＞ このメッセージは、本来なら「Withに対応するEnd Withがありません」となるべきものです。

　これまた本筋とはそれるコメントですみませんが、With 句がなくて "End With" があると「End With に対応する With がありません」と出ますね。w

投稿者 : higeru     投稿日時 : 2023/09/11(Mon) 16:14:20

---

■[返信 2] まささん（2023-09-11 14:29:08）の記事
＞ ■[返信 1] さんこうさん（2023-09-11 13:58:49）の記事
＞ こんな感じになるということでしょうか。

　そんな感じです。

＞ ws02.Range("AR" & i) = WorksheetFunction.VLookup(Buf(L), .Range("B:O"), 14, False)　'配列の値を検索値に入れVLOOKUP

　ここで VLookup でヒットしないとエラーになるので、そのための細工が要ります。またヒットしたときには Exit For しないといけません。

　それからこれまた本質的なツッコミではないですが、

＞ Dim ws01, ws02 As Worksheet
＞ Dim i, L, lRow As Long

これはよろしくありません。これだと ws02, L,lRow は Variant 型になります（Variant なので実害はないですが）。ここは正しいお作法として

　Dim ws01 As Worksheet, ws02 As Worksheet

と書きましょう。