Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
ワークシート関数Vlookup 繰り返し処理方法
投稿日時: 20/08/15 23:14:00
投稿者: さんたな

普段エクセルで使っているVLookup関数をVBAで実施してみたく
ネットみて真似しているのですが繰り返し処理がの記述方法がわかりません。
 
A列にデータが入っている箇所のみ、B列に結果を表示
どのように繰り返し処理をしたらよいのでしょうか。
「for」だと、2から50までデータが反映されてしまいます。
 
Sub macro()
    Dim i As Long
    For i = 2 To 50
   On Error Resume Next
   Cells(i, 2) = WorksheetFunction.VLookup(Cells(i, 1), Range("D2:E11"), 2, False)
   If Err.Number > 0 Then Cells(i, 2) = "-"
  Next i
End Sub
 
 
A列   B列    D列   E列
データ1 100   データ1 100
データ2 200   データ2 200
データ1 100
データ2 200
データ1 100
 

回答
投稿日時: 20/08/16 00:16:36
投稿者: simple

色々やり方はあります。

Sub macro1()
    Dim i As Long
    On Error Resume Next
    For i = 2 To 50
        Cells(i, 2) = WorksheetFunction.VLookup(Cells(i, 1), Range("D2:E11"), 2, False)
        If Err.Number > 0 Then Cells(i, 2) = "-"
        Err.Clear
    Next i
End Sub

Sub macro2()
    Dim i As Long
    Dim v As Variant
    For i = 2 To 50
        v = Application.VLookup(Cells(i, 1), Range("D2:E11"), 2, False)
        If IsError(v) Then
            Cells(i, 2) = "-"
        Else
            Cells(i, 2) = v
        End If
    Next i
End Sub

Sub macro3()
    Dim i As Long
    Dim v As Variant
    For i = 2 To 50
        If WorksheetFunction.CountIf(Range("D2:D11"), Cells(i, 1)) > 0 Then
            Cells(i, 2) = WorksheetFunction.VLookup(Cells(i, 1), Range("D2:E11"), 2, False)
        Else
            Cells(i, 2) = "-"
        End If
    Next i
End Sub

回答
投稿日時: 20/08/16 00:25:02
投稿者: simple

真ん中のものは、
Application.VLookupがエラーが発生して処理が止まることはなく、
ただ、エラー値だけを返すという振る舞いを利用するものです。
ポイントは返り値をVariant型変数で受けることです。
 
このあたりは、当サイトの「即効テクニック」に詳細説明されています。参考にしてください。
「ワークシート関数をVBAで使用する(WorksheetFunctionプロパティ)」
https://www.moug.net/tech/exvba/0100035.html
 
昔は、Applicationを使ってワークシート関数を読んでいましたが、
その後Worksheetfunctionが導入されたので、
本来はWorksheetfunctionを使うべきですが、
上記の方法はエラー処理を入れずに済むので、結構使うことが多いですね。

回答
投稿日時: 20/08/17 15:43:37
投稿者: simple

ああそうか、単に

If Cells(i, 1).Value <> "" Then
を入れるだけの話だったのかな。

回答
投稿日時: 20/08/17 16:45:16
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

A列にデータが入っている箇所のみ、B列に結果を表示 る
どのように繰り返し処理をしたらよいのでしょうか。
 「for」だと、2から50までデータが反映されてしまいます。

空白セルの場合。
>Cells(i, 2) = "-"
が実行されてしまう
ということですか?
 
1つは、
A列のデータが入っている最終セルを使います。
 
For i = 2 To Range("A" & Rows.COunt).End(xlUp).Row
 
途中に空白セルが存在する場合は、
If Cells(i, "A").Value <> "" Then
  'Vlookup
End If
で、空白セルを回避します。
 
 

投稿日時: 20/08/29 09:20:57
投稿者: さんたな

最後にいただいたこのレコードを入れたら、思っていた処理ができました。
いろいろとみていただきありがとうございます。
伝え方って難しいですね。
ありがとうございました。
 
For i = 2 To Range("A" & Rows.COunt).End(xlUp).Row
 
 

WinArrow さんの引用:
引用:

A列にデータが入っている箇所のみ、B列に結果を表示 る
どのように繰り返し処理をしたらよいのでしょうか。
 「for」だと、2から50までデータが反映されてしまいます。

空白セルの場合。
>Cells(i, 2) = "-"
が実行されてしまう
ということですか?
 
1つは、
A列のデータが入っている最終セルを使います。
 
For i = 2 To Range("A" & Rows.COunt).End(xlUp).Row
 
途中に空白セルが存在する場合は、
If Cells(i, "A").Value <> "" Then
  'Vlookup
End If
で、空白セルを回避します。