时间戳

首页 > 解决方案 > 两个工作簿之间的 VLookup 与 ISNA 功能相结合。运行时错误“1004”：应用程序定义或对象定义错误

vba - 两个工作簿之间的 VLookup 与 ISNA 功能相结合。运行时错误“1004”：应用程序定义或对象定义错误

问题描述

我正在研究 ws1，它是“新”工作簿中的工作表。它在第 1 栏中包含账单编号，在其他栏中包含其他相关信息。我想在另一个名为“PDA”的工作簿和该工作表中查找名为“Sheet1”的工作表，该工作表在 F 列中有帐单编号，我想在 G 列中复制我想要复制的数据。如果帐单编号不存在于 PDA> Sheet1，然后我想返回值 0，因为我必须稍后将金额相加。我已经写了这段代码，但它给出了错误。请帮我解决这个问题。我在这一行遇到错误

.Cells(i, 4) = "=If(ISNA(VLookup(.Cells(i, 1).Value2, y, 2, False)),0,VLookup(.Cells(i, 1).Value2, y, 2，假））”

Dim i As Long
Dim x As Range
Dim b As Range
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks.Open("C:\Users\mrisingh\Desktop\6-Eccs Billing List.xlsx")
Set x = wb1.Worksheets("Billing Sheet").Range("B2:E100000")
Set ws1 = Workbooks("New").Worksheets("Reconciliation")
With ws1
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 3) = Application.VLookup(.Cells(i, 1).Value2, x, 4, False)
Next i
End With
Set b = wb1.Worksheets("Billing Sheet").Range("B2:K100000")
With ws1
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 9) = Application.VLookup(.Cells(i, 1).Value2, b, 10, False)
Next i
End With
wb1.Close savechanges:=False

Dim y As Range
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Users\mrisingh\Desktop\PDA.xlsx")
Set y = wb2.Worksheets("Sheet1").Range("F845:G10000")
With ws1
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 4) = "=If(ISNA(VLookup(.Cells(i, 1).Value2, y, 2, False)),0,VLookup(.Cells(i, 1).Value2, y, 2, False))"
Next i
End With
wb2.Close savechanges:=False

标签： vbaexcelvlookup

解决方案

您正在将 VBA 片段混合到 Excel 公式中 - 这是行不通的。Excel 不知道是什么.Cells(i, 1).Value2或y意味着什么，但这就是您在单元格中写入的内容。另一方面，VBA 不会像在常量字符串（引号内）中那样替换.Cells(i, 1).Value2或替换y范围本身，并且 VBA 会单独保留这些字符串。

所以第一次尝试是写（未经测试，因为我没有你的数据，但你希望能明白）

dim lookupAdr as string, f as string
lookupAdr = "[" & wb2.name & "]" & "Sheet1!" & y.address
f = "=If(ISNA(VLookup(" & .Cells(i, 1).address & ", " _
    & lookupAdr & ", 2, False)),0,VLookup( " _
    & .Cells(i, 1).address & ", " & lookupAdr & ", 2, False))"
Debug.Print lookupAdr
Debug.Print f
.Cells(i, 4).formula = f

请注意，我首先将查找的公式和地址写入字符串变量 - 这有助于查找错误。另请注意，您应该始终将公式写入.formula单元格的属性，而不是.value属性。

但是，还有改进的余地：除非您仍在使用 Excel 2003，否则您可以使用IFERROR公式，这样您就不必重复VLOOKUP- 部分。这将导致一个更简单的公式（再次未经测试）：

 f = "=IFERROR(VLookup( " & .Cells(i, 1).address & ", " & lookupAdr & ", 2, False), 0)"

作为一般规则，当您尝试在 VBA 中创建公式时，首先采用不同的方式通常会有所帮助：将您想要的最终公式写入一个单元格，转到 VBA 编辑器，打开即时窗口(按[Ctrl]+[G]）并输入? activecell.formula。然后，编写您的代码并将您在 VBA 中创建的公式与您在即时窗口中看到的公式进行比较。

并且，作为建议：使用有意义的变量名。y对于范围变量来说，这并不是一个好名字 - 使用类似lookupRange. 每个会阅读代码的人（包括你在内！）都会感激不尽。

推荐阅读