如何输入相同的值来代替 Vlookup `#N/A` [英] How can I input same value in place of Vlookup `#N/A`

问题描述

我想更新其简短格式的端口代码.假设我有端口代码 DMM.我VLOOKUP这个单元格针对SADMM.这意味着如果我输入DMM，我会得到SADMM.但是如果我没有DMM，它会显示#N/A.而不是这个我想要 DMM;如果 VLOOKUP 找不到值，则为原始值.

I want to update Port codes from its short form. Suppose I have port code DMM. I VLOOKUP this cell against SADMM. It means if I type DMM, I will get SADMM. But if I dont have DMM, it will show #N/A. Instead of this I want DMM; the original value if VLOOKUP cant find the Value.

代码

Sub Vlookup_POD()
    On Error Resume Next
    
    Dim rng As Range, FinalResult As Variant, Table_Range As Range, LookupValue As Range

    Set rng = Sheets("Sheet3").Range(Range("I14"), Range("I14").End(xlDown))

    Set Table_Range = Sheets("Pod").Range("A1:B25")

    Set LookupValue = Sheets("Sheet3").Range(Range("I14"), Range("I14").End(xlDown))
    'Range ("I14:I500")

    FinalResult = Application.WorksheetFunction.VLookup(LookupValue, Table_Range, 2, False)
    rng = FinalResult
End Sub

推荐答案

我通常使用 IsError() 来捕获这些类型的错误.这是一个例子.

I usually use IsError() to trap these kind of errors. Here is an example.

FinalResult = Application.VLookup(LookupValue, Table_Range, 2, False)

If IsError(FinalResult) Then FinalResult = LookupValue

rng.Value = FinalResult

注意:如果我想捕获特定错误，那么我使用 CVERR()

Note: If I want to trap a specific error then I use CVERR()

还有一个提示.避免不必要地使用On Error Resume Next.仅在需要时使用它.或者更好的是，进行正确的错误处理.

One more tip. Avoid the use of On Error Resume Next unnecessarily. Use it only when required. Or better still, do correct error handling.

这篇关于如何输入相同的值来代替 Vlookup `#N/A`的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！