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"
A:
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
Michael
2010-06-23 20:27:59