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