CODEWITHSUNDEEP
excelvba
user2774993
user2774993

Reputation: 87

Excel macro: ignore error

I'm using vba code to vlookup values from an array. Some of the results are "#N/A" and there will be a green triangle on the left top of the cell. I want to remove the green triangle (ignore the error) automatically, but the "#N/A" result should be remained, just remove the green triangle.

Anyone knows how to do this?

Thanks in advance.

Upvotes: 2

Views: 13508

Answers (4)

Alpha
Alpha

Reputation: 2462

You can use this formula:

=IFERROR(VLOOKUP([Your Value], [Your Range], [Your Column], FALSE), NA())

Upvotes: 0

Profex
Profex

Reputation: 1390

It seems as though you can only ignore one cell at a time or it will give you an Application-defined or object-defined error

So, you'll need something like this...

Dim Cell As Range, Target As Range

    Set Target = Range("C10:D17")
    For Each Cell In Target
        Cell.Errors(xlEvaluateToError).Ignore = True
    Next

Also, see XlErrorChecks Enumeration (Excel) for a list of all the errors.

Upvotes: 3

Tim
Tim

Reputation: 21

You need code that looks something like this:

Dim r as Range
...
r.Errors.Item(xlEvaluateToError).Ignore = True

This will clear the error triangle (xlEvaluateToError equals 1, which is why the previous person had Item(1) in their answer).

The other errors that can occur can be cleared in a similar way, by changing the number of the Item, e.g. I've used a block of code like this to put a value (that might look like a number, but I want to ensure is treated as text) into a cell:

With Target_Sheet.Cells(RowNum, ColNum)
    .NumberFormat = "@"     ' Force the cell to be treated as text
    .Value = whatever_value_im_trying_to_set
    .Errors.Item(xlNumberAsText).Ignore = True
End With

See the Excel help for Errors.Item Property for the full list of error codes you can check or ignore.

You can also query the value of .Errors.Item(xlNumberAsText).Value (or the other error codes) to see if the error is occurring, but you can't set .Value to clear the error.

Upvotes: 2

steveo40
steveo40

Reputation: 931

If you want the green triangle to go away you need to set the Error.Ignore property to true. For example, if you have a formula such as "=1/0" in cell A1, then in VBA you could do something like this:

Dim r as Range
Set r = Range("A1")
r.Errors.Item(1).Ignore = True

And the green triangle will go away.

Upvotes: 1

Related Questions