I am creating a report in my ASP.NET application. The report outputs to Excel using the Excel interop. Some calculations in the report cause Excel to display "INF" or "NaN". I want to force these calculations to zero so that the report displays zero and NOT "INF" or "NaN".
Are you looking to do this on the Excel side, after the value is populated as "Inf" or "NaN"? If so, running this VBA code will replace all your non-numeric values with 0.
Sub fixValues() Dim areaToSearch As Range, cell As Range Set areaToSearch = Sheet1.Range("A1:A50") For Each cell In areaToSearch If IsNumeric(cell.Value) = False Then cell.Value = 0 End If Next cell End Sub