收录日期:2019/04/18 21:06:54 时间:2015-06-17 09:45:04 标签:c#,vb.net,excel-vba

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