Replace #REF! from VLOOKUP reference with "0"

[maruapo]

One part of my Macro inserts a VLOOKUP formula into a cell, one cell per month/year per department. The file at the other end of the VLOOKUP is a financial planning file, so sometimes it contains no data for a particular month for that department. This results in the cell displaying "#REF!" in that cell since the accompanying VLOOKUP reference contains no data.

I need a line or two of VBA that displays "0" (zero) in these instances, either by writing over the VLOOKUP formula for that cell or some form of an ISNA formula that displays "0". I can't do a simple find and replace, because the problematic cells contain a formula, and not "#REF!".

Here's the section containing the aforementioned problem. Notice my three attempts, now comments, at fixing this. Any suggestions?

'TY FPS Plan calculation [added July 29, 2005]'
ActiveCell.Offset(-3, 1).Select

ActiveCell.Formula = "=VLOOKUP(" & ActiveCell.Offset(-1, -3).AddressLocal & "," & "'\\f0807p3\share\Reserve Stock\FPS Plans\[FPS Plan.xls]EOM FORECAST REPORT'!$A$2:$AC$36,('\\f0807p3\share\Reserve Stock\FPS Plans\[FPS Plan.xls]EOM FORECAST REPORT'!E$69),FALSE)"
ActiveCell.NumberFormat = "0.0,"

'If ActiveCell = "#REF!" Then ActiveCell = "0.0"
'If ActiveCell = "" Then ActiveCell = "0.0"
'ActiveCell.Replace What:="#REF!", replacement:="0.0"

ActiveCell.Replace What:="", replacement:="0.0", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
ActiveCell.Copy
Range(ActiveCell.Offset(0, 1).Address, ActiveCell.Offset(0, datecount - 1).Address).Select
ActiveSheet.Paste
Range(cell1).Select

 

[AussieRob]

I am sure there is a better way, but this is quick and dirty:

For a = 1 to ???

    Selection.SpecialCells(xlCellTypeFormulas, 16).Select
    ActiveCell = 0

Next a

This is VBA for Ctrl G - Special - Formulas - Errors. That is it will go to each error on your sheet and replace it with a 0. Keep in mind it will replace all errors inclduing #Div/0 with 0.

 

[maruapo]

Muchas grassyass!
The Ctrl+G formula worked, but not where I wanted to put it; after much trial and error did I figure out exactly WHERE it needed to go. It didn't work in the middle of my code as listed in my last post because the Macro needs to activate cells and paste them in another sheet. The Ctrl+G formula caused confusion because the Macro didn't know what cell to activate. In short, I pasted the data (including the errors) into a new workbook, then the Ctrl+G formula worked perfectly.

Thankx again!

maruapo