Highlight formula results that have a blank value

[Alex Wren]

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]

[vba]

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
[/vba]

[Alex Wren]

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]

[VBA]msgbox rng.cells.count[/VBA]