Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
For Eachで指定している値を利用してVlookup
投稿日時: 24/02/21 15:02:05
投稿者: Nol69

For Each文で指定している値を利用する内容がいくつかあり、
VLOOKUPが上手く利用できず作業が止まってしまっておりますのでお助け頂けますと幸甚です。
 
・なぜVLOOKUPでないといけないのか
 シートAにある4行目の値 & シートBにある4行目6列目と一致する値 の別シートを捜索するため
 ※シートAの4行目は必要なだけのデータしかないもの、シートBのデータは不要データも含んでいるため、
  純粋にシートBを見るだけでは済まないため
 
 
以下現在組んでいるVBA
 
 Dim En1 As Range
          Dim LstRight1 As Long
           
          LstRight1 = Cells(Columns.Count, 1).End(xlToRight).Row
          For Each En1 In wb1.Sheets("A").Range("I4" & LstRight1) '指定レンジから最後列まで
           
          Dim En As String
          En = En1.Value
                     
          Dim Name As String
          Name = Application.WorksheetFunction.VLookup(En, wb1.Sheets("B").Range("I11:N1221"), 6, False) '欲しい値
 
 



Next En1

回答
投稿日時: 24/02/21 15:39:51
投稿者: WinArrow

説明は、ほとんど理解できません。
もっと、他人に分かるように説明しましょう。
 
 
2ヶ所のコードについて、教えてください。
1番目
>LstRight1 = Cells(Columns.Count, 1).End(xlToRight).Row
このコードを実行した結果は、想定通りなんですか?
 
Cells(Columns.Count, 1)は、Range("A16384")ですから、
16384行目を検査していることになりますが、なぜ、16384行目なんですか?
 
もう一つは、シートで修飾していませんが、どのシートを参照しているのでしょう?
 
2番目
>For Each En1 In wb1.Sheets("A").Range("I4" & LstRight1)
の中の
wb1.Sheets("A").Range("I4" & LstRight1)
は、セル範囲になっていませんが・・・・大丈夫ですか?

回答
投稿日時: 24/02/21 17:25:09
投稿者: WinArrow

勝手に解釈したコードを書きます。
動作を保証するものではありません。

Dim LastRow As Long
Dim myName As String
 
    With wb1.Sheets("A")
        LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
        For Each En1 In .Range("I4:I" & LastRow)
            myName = WorksheetFunction.VLookup( _
                En1.Value, wb1.Sheets("B").Range("I11:N1221"), 6, False) '欲しい値
        '・
        '・
        '・
        Next En1
    End With

回答
投稿日時: 24/02/22 09:48:21
投稿者: simple

VLOOKUPで検索する値たちのセルが入ったセル範囲を正確に説明されたらいかがですか?
特定の行の、横に長いセル範囲のようにも思えるのですが、いかがですか?
そこを明確にされたらいかがでしょうか。
 
最終行を求める例は、(既に提示されているように)
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
のような書き方ですが、
最終列を求める場合は、
lastColumn=Cells(1, Columns.Count).End(xlToLeft).Column
のような書き方ですね。
 
もし横長のセル範囲であれば、こんな感じでしょうか。
 

Sub test()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastColumn As Long

    set wb = ......
    Set ws = wb.Sheets("A")
    
    lastColumn=ws.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each rng In ws.Range("I4", ws.Cells(4, lastColumn))
        '''ここにVLOOKUPを使った処理を書きます
    Next
End Sub

 
VLOOKUPが該当なしになるときの扱いに注意が必要ですが、
まずは、それまでのところを進められたらよろしいかと思います。

回答
投稿日時: 24/02/24 10:38:34
投稿者: simple

反応頂けないですが、追記しておきます。
(1)

Sub test()
    Dim wb    As Workbook
    Dim ws    As Worksheet
    Dim rng   As Range
    Dim lastColumn As Long
    Dim v     As Variant

    Set wb = ThisWorkbook   '想定です
    Set ws = wb.Sheets("A")

    lastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each rng In ws.Range("I4", ws.Cells(4, lastColumn))
        v = Application.VLookup( _
                rng.Value, wb.Sheets("B").Range("I11:N1221"), 6, False)    '欲しい値
        If Not IsError(v) Then      ' vがエラー値でなければ、利用する
            '処理をここに書きます
        Else
            MsgBox rng.Value & "に対応するデータが見つかりませんでした"
        End If
    Next
End Sub

(2)
ワークシート関数を使う場合の注意点があります。
    v = Application.WorksheetFunction.VLookup( _
             rng.Value, wb.Sheets("B").Range("I11:N1221"), 6, False) 
とした場合、対応するキーが参照先になければ、エラーになり処理が止まります。
止まらないようにするには、下記test2のようなエラー処理を予めコーディングしておく必要があります。
(以下では簡単に、検索値セルがA1で、C1:D4にある表を検索する例でコードを示します。)
Sub test2()
    Dim v
    On Error GoTo errline
    v = Application.WorksheetFunction.VLookup( _
            [A1].Value, Range("C1:D4"), 2, False)    
    '' 以下にvを使った処理が続く
    MsgBox v
    Exit Sub
errline:
    MsgBox [A1].Value & "  に対応するデータが見つかりませんでした"
    v = ""
    Resume Next
End Sub

(3)
こうした場合の便法として、Application.VLookupのような書き方が使われることも多いでしょう。
この場合は、エラーで止まらずに、エラー値が返ってきます。
それをIsError関数で判定して処理を分岐させることができるので記述が簡単になります。
Sub test3()
    Dim v
    v = Application.VLookup( _
            [A1].Value, Range("C1:D4"), 2, False)   
    If IsError(v) Then
        v = ""
        MsgBox [A1].Value & "  に対応するデータが見つかりませんでした"
    End If
    '' 以下にvを使った処理が続く
    MsgBox v
End Sub
Application.VLookupという使い方は、Application.WorksheetFunctionが導入する以前からある
古い書き方ですが、記述が簡便になることもあって、相変わらず使われることが多いですね。
(ワークシート関数Matchを使う場合にも同じ議論ができますので念頭に置くとよいと思います。)
(4)ついでに。
なお、ワークシート関数IFErrorも使えますので、以下のような書き方もできます。
Sub test4()
    Dim v
    v = Application.IFError( _
            Application.VLookup( _
            [A1].Value, Range("C1:D4"), 2, False), "")   
    Debug.Print v
End Sub

(1)と(3)が重要かと思います。

トピックに返信