为什么isError（）在excel VBA中与vlookup语句配合使用 [英] Why doesn't isError( ) work with a vlookup statement in excel VBA

问题描述

我使用的是excel 2007，并且已经创建了一个包含三个vlookup（）语句的UDF。该函数应该返回所有三个vlookup语句的总和。在大多数情况下，只有两个vlookup（）语句将返回有效值，第三个语句将导致NA，因为查找值不包括在查找范围内。

I'm using excel 2007 and have created a UDF that includes three vlookup() statements. The function is supposed to return the sum of all three vlookup statments. In the majority of cases, only two the vlookup() statements will return a valid value the third statement will result in an NA because the lookup value is not included in the lookup range.

我试图通过使用以下方式捕获错误并返回零：

I have tried to trap the error and return a zero by using:

1. Application.WorksheetFunction.iferror（vlookup（...），0）

1. Application.WorksheetFunction.iferror(vlookup(...) ,0)

然后使用If iserror（vlookup（））的条件。 ..

A conditional that uses If iserror(vlookup()) then ...

但我看起来似乎没有办法工作。如果我注意到vlookup，我知道是创建错误，一切正常工作。

but I can't seem to get either approach to work. If I comment out the vlookup that I know is creating the error everything works as expected.

有谁知道为什么iserror（0和iserror（）似乎没有工作，或者也可能是一种可行的替代方法。

Does anyone know why iserror(0 and iserror() don't seem to be working or perhaps an alternative approach that will work.

更新：

以下是三个vlookup函数：

Here are the three vlookup function:

product2 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productA, lookuprng, offset, False), 0)

product3 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productB, lookuprng, offset, False), 0)

product4 = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(productC, lookuprng, offset, False), 0)

推荐答案

您可以使用以下内容来捕获错误：

You can trap the error with the following:

Sub HandleVlookupErrors()
    Dim result As Variant

    result = Application.VLookup(productA, lookuprng, offset, False)
    If IsError(result) Then result = 0

End Sub

有关完整说明，请参阅工作表函数内的错误处理。

For a full explanation, please see Error Handling Within Worksheet Functions.

这篇关于为什么isError（）在excel VBA中与vlookup语句配合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！