Solved: DISPLAY "0" INSTEAD of #NUM!

[jammer6_9]

I have used a formula =VLOOKUP(F5,$AX$35:$BB$81,5,0) but IF LookUp value is not available in the table, result is #NUM!.. Is it possible not to show #NUM! and instead display NULL or "0" BY using formula or VBA?


[vba]
Dim myRange As Range
Set myRange = Worksheets("TOP PERFORMERS").Range("C4:F50")

If IsError(myRange.Value) Then
myRange.Value = "0"

End If
[/vba]

[rory]

The result should be #N/A if the value is not found. To correct it you can use:
=IF(ISERROR(VLOOKUP(F5,$AX$35:$BB$81,5,0)),0,VLOOKUP(F5,$AX$35:$BB$81,5,0))

This will return 0 even if the lookup value is found but the returned value is actually an error (e.g. there's a #VALUE error in the data)

[jammer6_9]

That formula works perfect... Thanks... But can we make it in VBA?

[YellowLabPro]

Jammer,
Something that helped me was to do these in the macro recorder.
One of the members showed me, that if you remove the "=" in the formula, start the recorder, place the "=" in front of the formula, stop the recorder. You will have the syntax for VBA.
You may need to tweak the A1 notation from R1C1....

[rory]

You asked for formula or VBA. Formula is better as it is dynamic; if you use VBA you have to run it every time the formulas recalculate to ensure the data is up to date and you also need to worry about replacing the formulas that you deleted in case the data has been changed in your data source.

[Bob Phillips]

Not efficient, but ...

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[vba]

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim rtn
If Sh.Name = "TOP PERFORMERS" Then
On Error Resume Next
rtn = Application.VLookup(Sh.Range("F5").Value, Sh.Range("AX35:BB81"), 5, 0)
On Error GoTo 0
If IsError(rtn) Then rtn = 0
Sh.Range("H1").Value = rtn '<===change H1 to your target cell
End If
End Sub
[/vba]

[jammer6_9]

Thanks to all your reply & advice.

[rory]

Can I ask why you use the On Error Resume Next and On Error Goto 0 there? Habit, or in case behaviour changes in a different version?