SOLVED: Finding #VALUE! as a value

[bmwguy525]

Hi guys, first post here. I looked around using the search function but couldn't find what I was looking for, so hopefully I can find some help here.

I need to delete all rows that contain "#VALUE!" in Column B. My problem lies in that I can't make VBA find cells that contain the value "#VALUE!". The setup:

Sheet1
Column A = list
Column B = DGET function in Sheet 2 for item match in Column A
There are then a list of values that fill for Column B (21588 cells), however there are certain items in Column A Sheet1 that have no recipricate in Column A Sheet2, therefore in Column B Sheet1 I get a number of cells that return the #VALUE! error.

I tried running a find and delete macro for cell value = #VALUE!, but nothing happens. When I was messing around with Conditional Formatting, I noticed that Excel doesn't recognize "#VALUE!" as a searchable cell value, even after formatting to text!

The list of returns in Column B is all numbers, so I'm assuming that creating something that deletes all rows where Column B is not a number is doable? This seems like it shouldn't be a big deal, but I'm coming up blank at every corner

Thanks in advance for any help!

[mdmackillop]

Hi bmwguy,
Welcome to VBAX
[vba]Sub DeleteErrors()
Columns("B:B").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

[/vba]

[daniels012]

welcome BMWguy525,

First I would put this formula down an open Column:

 
=IF(ERROR.TYPE(C11)=3,"Y","N")

This checks for the #VALUE! error.
Then just delete all rows with Y in them.

Michael

[bmwguy525]

Thanks for the quick help guys! I figured there was a couple ways to get it done, I just couldn't pull my head out of the program for a second to get a clear look at it :P

[mdmackillop]

Glad to help.
To record a macro using SpecialCells, press F5/Special then select your option.

[crazyfray]

Also, you might find the ISERROR(checkvalue) function useful. If you have #VALUE errors in some cells due to a calculation that you have made as a formula, using ISERROR is a common way of saving yourself from using another column.

For example, if have a VLOOKUP that isn't finding anything you get the #NA error. Quite often, you don't want an error if something isn't found, just a blank. In this case, you'd use something along the lines of:

=IF(ISERROR(VLOOKUP(A1, B1:B10, 1, FALSE)) = true, "", VLOOKUP(A1, B1:B10, 1, FALSE))

Which would try and find the value in A1 in B1:B10. If it's not found, you'd get a blank, rather than a #NA error. If it is there, it returns the value.

The =TRUE part isn't needed, as iserror() will assume that you want to know if it's true unless you specifiy otherwise, but for the sake of completeness it's in the formula above.

It's just a more generalisable solution (though error.type is more specific, which is sometimes what you want!).

EDIT: Bah. That's what I get for posting when it's late. I haven't used DGET, but the principle is the same :P. Using the example in the excel help, no reason that DGET can't use ISERROR - using the example data in the helpfile - =IF(ISERROR(DGET(A4:E10,"Yield",A1:A3)), "", (DGET(A4:E10,"Yield",A1:A3)))
...and if it has to be VBA, mdmackillop already answered.

[bmwguy525]

cracyfray,
Thanks for the info! On this particular project I would've ended up in the same situation in the end, having to delete the rows with the blank cells. But I can certainly use that for later projects, as I never thought about formulizing like that.