CODEWITHSUNDEEP
excelvbaerror-handling
user52363
user52363

Reputation: 13

VBA: Ignore the Variable not set error

I need to populate a range of data based on another range of data. For example, if the 1st range contains accrued admin expense I would populate "closing Accruals (Admin Expense)" into another range. My code below works if it manages to find all of the fields. However, if one of the fields is missing it returns error variable not set and stop running.

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

I would need the code to ignore the error and continue running so that it can continue to populate those that are found. I have tried to use On Error Resume Next but it don't seem to be working. Any idea how i could get the system to ignore this error? Thanks in advance

Upvotes: 0

Views: 666

Answers (2)

MacroMarc
MacroMarc

Reputation: 3324

If the range to be searched is indeed the same for each search text, and the offsets are indeed all the same(4 and 5), then it's nicer to create a function and call the function, passing the strings as parameters. Put this function ONCE in your module:

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

Then call each bit of code in your sub like so:

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...

If your function was a little bit different, you could put error handling in your functions for example if you were working with numbers, you could check for division by zero and address it in your function/

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

I assume you have declared (Dim'd) FindAdmin as a Range You could put a single On Error Resume Next at the beginning of your .Find's, and, to turn off error checking, On error goto 0 at the end of all those.

But, to avoid missing other errors, a construct such as the following would be more useful, and also give you the opportunity for other options if the search phrase is not found:

Set FindAdmin = Worksheets("IVES").Range("A1:Z350").Find("** ACCRUED ADMINISTRATION EXPENSE", LookIn:=xlValues, lookat:=xlWhole)
If Not FindAdmin Is Nothing Then
    With FindAdmin
        .Offset(, 4) = "Budget"
        .Offset(, 5) = "Closing Accruals (Admin Expense)"
    End With
End If

Upvotes: 2

Related Questions