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