Solved: #DIV/0! ERROR

[PAB]

Good evening,

I have some code that works perfectly except that one of the output cells "nType(i)" is equal to ZERO and when I try and divide "TotalComb" by "nType(i)" it gives me an ERROR.
What I would like is for it to place a ZERO in the cell and then continue as normal.
The piece of code is between the *******'s

[vba]' Display Solution:
For i = 22 To 323
Total = Total + i
Cells(RowCount, "B").Value = "B + A + A + C + D + E + F = " & i
Cells(RowCount, "C").Value = nType(i)
Cells(RowCount, "D").Value = 100 / TotalComb * nType(i)
*******************************************************************
Cells(RowCount, "E").Value = TotalComb / nType(i)
*******************************************************************
Cells(RowCount, "F").Value = "Draws"
' Format Output
Cells(RowCount, "B").HorizontalAlignment = xlLeft
Cells(RowCount, "B").Resize(1, 5).Borders.LineStyle = xlContinuous
Cells(RowCount, "C").NumberFormat = "##,###,##0"
Cells(RowCount, "D").NumberFormat = "##0.00"
Cells(RowCount, "E").NumberFormat = "##,###,##0.00"
Cells(RowCount, "F").HorizontalAlignment = xlRight
RowCount = RowCount + 1
Next i
[/vba]

Thanks in advance,
PAB

[PAB]

I have entered "On Error Resume Next" ...

[vba] Cells(RowCount, "E").Value = TotalComb / nType(i)
On Error Resume Next[/vba]
... but it leaves the cell BLANK.
I would like it to put a ZERO in the cell.

Regards,
PAB

[mikerickson]

[VBA]Cells(RowCount, "E").Value = Val(CStr(TotalComb / nType(i)))[/VBA]

(As a mathematician, I maintain that #DIV/0 or [blank] are more accurate results than 0 in the case of dividing by zero.)

[Aussiebear]

Try the following

[vba]' Display Solution:
For i = 22 To 323
Total = Total + i Cells(RowCount, "B").Value = "B + A + A + C + D + E + F = " & i
Cells(RowCount, "C").Value = nType(i)
If nType(i).Value = 0 Then
Cells(RowCount, "D").Value = 0
Else
Cells(RowCount, "D").Value = 100 / TotalComb * nType(i)
End if
If nType(i).Value = 0 Then
Cells(RowCount,"E").Value =0
Else
Cells(RowCount, "E").Value = TotalComb / nType(i)
End If
Cells(RowCount, "F").Value = "Draws"
' Format Output
Cells(RowCount, "B").HorizontalAlignment = xlLeft
Cells(RowCount, "B").Resize(1, 5).Borders.LineStyle = xlContinuous Cells(RowCount, "C").NumberFormat = "##,###,##0"
Cells(RowCount, "D").NumberFormat = "##0.00"
Cells(RowCount, "E").NumberFormat = "##,###,##0.00"
Cells(RowCount, "F").HorizontalAlignment = xlRight
RowCount = RowCount + 1
Next i[/vba]

[PAB]

Hi mikerickson, I tried your solution but unfortunately it still gave me the #DIV/0! ERROR.

Hi Aussiebear, I tried your solution but it came up with a "Compile error: invalid qualifier" on "nType(i)".
I took out the ".Value" part of the "If ... Else ... End If" and it works great.

[vba] If nType(i) = 0 Then
Cells(RowCount, "D").Value = 0
Else
Cells(RowCount, "D").Value = 100 / TotalComb * nType(i)
End If
If nType(i) = 0 Then
Cells(RowCount, "E").Value = 0
Else
Cells(RowCount, "E").Value = TotalComb / nType(i)
End If
[/vba]
Thanks to both of you for your time and effort on this.

Kind regards,
PAB