excel - VBA:忽略变量未设置错误

Question

我需要根据另一个数据范围填充一系列数据。例如，如果第一个范围包含应计管理费用，我会将“结算应计(管理费用)”填充到另一个范围中。如果它设法找到所有字段，我的下面的代码就可以工作。但是，如果缺少其中一个字段，它将返回错误变量未设置并停止运行。

Set FindAdmin = Worksheets("IVES").Range("A1:Z350").Find("** ACCRUED ADMINISTRATION EXPENSE", LookIn:=xlValues, lookat:=xlWhole)
Range(FindAdmin.Address).Offset(, 4) = "Budget"
Range(FindAdmin.Address).Offset(, 5) = "Closing Accruals (Admin Expense)"
On Error Resume Next

Set FindAudit = Worksheets("IVES").Range("A1:Z350").Find("** ACC AUDIT", LookIn:=xlValues, lookat:=xlWhole)
Range(FindAudit.Address).Offset(, 4) = "Budget"
Range(FindAudit.Address).Offset(, 5) = "Closing Accruals(Audit Expense)"
On Error Resume Next

Set FindLegal = Worksheets("IVES").Range("A1:Z350").Find("** PAYABLE FOR FUND LEGAL - EXP", LookIn:=xlValues, lookat:=xlWhole)
Range(FindLegal.Address).Offset(, 4) = "Budget"
Range(FindLegal.Address).Offset(, 5) = "Closing Accruals (Legal Fees)"
On Error Resume Next

Set FindTax = Worksheets("IVES").Range("A1:Z350").Find("** PAYABLE FOR FUND TAX-EXPENSE", LookIn:=xlValues, lookat:=xlWhole)
Range(FindTax.Address).Offset(, 4) = "Budget"
Range(FindTax.Address).Offset(, 5) = "Closing Accruals (Tax Exp)"
On Error Resume Next

Set FindProf = Worksheets("IVES").Range("A1:Z350").Find("** ACCRUED OTHER PROF FEE", LookIn:=xlValues, lookat:=xlWhole)
Range(FindProf.Address).Offset(, 4) = "Budget"
Range(FindProf.Address).Offset(, 5) = "Closing Accruals (Other Prof Fee)"
On Error Resume Next

Set FindCustody = Worksheets("IVES").Range("A1:Z350").Find("** CUSTODY", LookIn:=xlValues, lookat:=xlWhole)
Range(FindCustody.Address).Offset(, 4) = "Budget"
Range(FindCustody.Address).Offset(, 5) = "Closing Accruals (Custody Exp)"
On Error Resume Next

Set FindMisc = Worksheets("IVES").Range("A1:Z350").Find("** ACCRUED MISCELLANEOUS EXPENSE", LookIn:=xlValues, lookat:=xlWhole)
Range(FindMisc.Address).Offset(, 4) = "Budget"
Range(FindMisc.Address).Offset(, 5) = "Closing Accruals (Misc Exp)"
On Error Resume Next

Set FindIRC = Worksheets("IVES").Range("A1:Z350").Find("** IRC FEE ACCRUAL",    LookIn:=xlValues, lookat:=xlWhole)
Range(FindIRC.Address).Offset(, 4) = "Budget"
Range(FindIRC.Address).Offset(, 5) = "Closing Accruals (IRC Fee)"
On Error Resume Next

我需要代码忽略错误并继续运行，以便它可以继续填充找到的那些。我曾尝试使用 On Error Resume Next，但它似乎不起作用。知道如何让系统忽略此错误吗？提前致谢

Answer 1

如果每个搜索文本的搜索范围确实相同，并且偏移量确实都相同(4 和 5)，那么最好创建一个函数并调用该函数，将字符串作为参数传递。将此函数一次放入您的模块中:

Function NewRange(txt As String, str1 As String, str2 As String)
    Dim r As Range
    Set r = Worksheets("IVES").Range("A1:Z350").Find(txt, LookIn:=xlValues, LookAt:=xlWhole)
    If Not r Is Nothing Then
        r.Offset(, 4) = str1
        r.Offset(, 5) = str2
    End If
End Function

然后像这样调用 sub 中的每一段代码:

Call NewRange("** ACCRUED ADMINISTRATION EXPENSE", "Budget", "Closing Accruals(Audit Expense)")

Call NewRange("** ACC AUDIT", "Budget", "Closing Accruals(Audit Expense)")

Call NewRange("** PAYABLE FOR FUND LEGAL - EXP", "Budget", "Closing Accruals (Legal Fees)")

ETC...

如果你的函数有点不同，你可以在你的函数中进行错误处理，例如，如果你正在处理数字，你可以检查除以零并在你的函数中解决它/