VBA Vlookup条件（如果有的话）源数据和vlookup范围在不同的工作表上

永远学习

有条件的Vlookup我需要您的帮助。我发现如果源数据中有vlookup值，则代码可以正常工作，但是一旦缺少值，它就会失败。另外，我还需要添加一个条件（“如果通过Lookup找到了值，则返回“旧”（从vlookup表的第二列））'如果未找到该值，则返回“ New”（只是没有出现的文本（来自vlookup表）。您能帮我吗？

Sub Vlookup_Condition()
Dim rng As Range
Dim i As Long

With ActiveSheet.Cells
    Set rng = .Range("A1:A" & .Cells(.Rows.count, 1).End(xlUp).row)

    For i = 2 To rng.Rows.count
        'If the value is found by Lookup, then return "Old" (from 2nd column in vlookup table)
        'If the value is NOT found, then return "New" (just text which is not coming from vlookup
        'table)

        rng.Cells(i, 2) = Application.WorksheetFunction.VLookup(.Cells(i, 1), Sheets("Lookuptable").Range("A:B"), 2, False)
    Next
End With

结束子

贾加迪什·达比鲁

根据您的困惑。我找到了这样的解决方案Russ

更新和测试

Sub Vlookup_Condition() 
Dim rng As Range 
Dim i As Long 

Application.ScreenUpdating = False 

Worksheets("DataFile").Activate 

Range("R2").Activate 

With Worksheets("DataFile").Cells 
Set rng = .Range("O1:O" & .Cells(.Rows.count, 1).End(xlUp).row) 

For i = 2 To rng.Rows.count 

rng.Cells(i, 4) = Application.VLookup(.Cells(i, 15), Sheets("Lookuptable").Range("A:B"), 2, False) 
If IsError(rng.Cells(i, 4)) Then
   If rng.Cells(i, 4) = CVErr(xlErrNA) Then ' Given if condition to change it from "#NA" to "New"
     rng.Cells(i, 4) = "New"
   End If
End If
Next 
End With 

Application.ScreenUpdating = True 

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。