PDA

View Full Version : Highlight formula results that have a blank value



Alex Wren
01-18-2007, 01:34 PM
Hello,

I am trying to create a bit of code to search a range and highlight any cells that have a blank result from a vlookup.

To explain further - I have a vlookup table in another sheet that contains some data and some empty cells. I need to write something that will look for the '0' that is shown as a result of a blank value (via the vlookup).

I was using


On Error GoTo FEError

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Select
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Select
MsgBox "Bad news - there is errors in this data! Cells with errors have been selected", , ""
before I changed the way the vlookup data was stored (I added all the empty cells so that it was easier for the client to add new data)

I have played with


Selection.Find(What:="0", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Selection.Replace What:="0", Replacement:="MISSING DATA", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
but it only selects real '0's and not the '0' resulting in a blank result via the vlookup.

Can anyone give me any pointers?

Alex

Bob Phillips
01-18-2007, 02:15 PM
Dim cell As Range
Dim rng As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If cell.Value = "" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell

If Not rng Is Nothing Then rng.Select

Alex Wren
01-18-2007, 03:06 PM
Thanks that seems to work rather well.

Do you know how I might produce a count of the blank cells as a msgbox?

Alex

mdmackillop
01-18-2007, 03:10 PM
msgbox rng.cells.count