PDA

View Full Version : Solved: #DIV/0! ERROR



PAB
12-26-2011, 08:01 PM
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

' 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


Thanks in advance,
PAB

PAB
12-26-2011, 09:38 PM
I have entered "On Error Resume Next" ...

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

Regards,
PAB

mikerickson
12-26-2011, 10:37 PM
Cells(RowCount, "E").Value = Val(CStr(TotalComb / nType(i)))

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

Aussiebear
12-26-2011, 10:46 PM
Try the following

' 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

PAB
12-27-2011, 07:01 AM
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.

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

Thanks to both of you for your time and effort on this.

Kind regards,
PAB