Divide Expression stops in Macro when Can't divide

[JUAN]

Hello,
have following sample code:

Dim i as long

For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A") = "SEM Total" Then
Cells(i, "D").Value = Cells(i, "C") / Cells(i, "B")
Cells(i, "G").Value = Cells(i, "F") / Cells(i, "E")
Works fine but if it cannot divide, the macro stops.
Example,
I could have in Col C 0 and Cold B 0, so this can't be
divided. 0/0 OR 1/0. Is there a way round this?
Please advise any info. would appreciate it alot.
Thanks
Juan

 

[Bob Kilmer]

Juan,
Try these ideas:

1. Check the input and substitute some suitable response when the values
don't make sense. Maybe return 0, raise an error, or display a message;
whatever makes sense in your situation.

Let x = Val(Cells(i, "B").Text): If x <= 0 Then Msgbox "Hey! Cut that
out!": Exit Sub: End If

2. Check the input and assign a default value when the inputs don't make
sense.

Let x = Val(Cells(i, "B").Text): If x = 0 Then Let x = 1

3. Try using On Error Resume Next. This should cause the code to just
continue if an error occurs. You will have to decide if the result of
skipping errors works in your case.

Dim i as long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A") = "SEM Total" Then
Cells(i, "D").Value = Cells(i, "C") / Cells(i, "B")
Cells(i, "G").Value = Cells(i, "F") / Cells(i, "E")
...

4. Catch the error and do something if it occurs.

Sub foo()
On Error Goto ErrHandler
Let x = Val(Cells(i, "B").Text)
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A") = "SEM Total" Then
Cells(i, "D").Value = Cells(i, "C") / x
....
Exit Sub
ErrHandler:
If Err.Number = 11 Then
Msgbox "Can't divide by zero! Try 1, instead.",,"Oops!"
Let x = 1
Resume
Else
Msgbox "Error: " & Err.Description,,"Oops!"
End If
End Sub

 

[ross]

Juan,
Sound ideas from Bob, all i have to add is this, that in a sheet i
have the error number was 6, not 11?
i used on

error to go to Singularity

Singularity:
If Err.Number = 6 Then
Resume Next
Else
End If

viz if it was a /0 issue it would skipp and carry on else, VB will
throw up an error message.

Good Luck

Ross

 

[Juan]

Hello Bob,
thanks for the ideas. I used the Resume Next which does
the trick. But I know that when it can't calculate it
leaves blank which its not a big deal but could I perhaps
put a Zero instead of leaving blank? I tried to
incorporate some of your ideas in my code but cant' seem
to work. Here's my code:
Dim i as long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A") = "SEM Total" Then
Cells(i, "D").Value = Cells(i, "C") / Cells(i, "B")
Cells(i, "G").Value = Cells(i, "F") / Cells(i, "E")
ElseIf Cells(i, "A") = "Sil Total" Then
Cells(i, "D").Value = Cells(i, "C") / Cells(i, "B")
Cells(i, "G").Value = Cells(i, "F") / Cells(i, "E")
End If
Next i

End Sub
Please advise where in my code I can perhaps make the
blank field a Zero.
Thanks,
juan

 

[Bob Kilmer]

First, I wonder why if Cells(i, "D") and Cells(i, "G") get the same value
(Cells(i, "C") / Cells(i, "B") and
Cells(i, "F") / Cells(i, "E"), respectively) , regardless of what Cells(i,
"A") is, why have the If-Then-Else block at all?

 

[Bob Kilmer]

Here's one way. In the loop that begins "For Each v In Array(Array(...", v
gets the value of each array within the outer array on each loop. The first
time, v(0) ="D", v(1) ="C", v(2)="B". The second time, v(0) ="G", v(1) ="F",
v(2)="E". The "If Err.Number <> 0 ..." block tests to see if an error
occured in the previous line. If so, the code assigns a zero to Cells(i,
v(0)).

Sub sub1()
Dim v As Variant
Dim i As Long
On Error Resume Next
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, "A").Text = "SEM Total" Then
For Each v In Array(Array("D", "C", "B"), Array("G", "F", "E"))
Cells(i, v(0)).Value = Cells(i, v(1)).Value / Cells(i,
v(2)).Value
If Err.Number <> 0 Then
Cells(i, v(0)).Value = 0
Err.Clear
End If
Next v
ElseIf Cells(i, "A") = "Sil Total" Then
For Each v In Array(Array("D", "C", "B"), Array("G", "F", "E"))
Cells(i, v(0)).Value = Cells(i, v(1)).Value / Cells(i,
v(2)).Value
If Err.Number <> 0 Then
Cells(i, v(0)).Value = 0
Err.Clear
End If
Next v
End If
Next i
End Sub

Bob

 

[Juan]

Hello Bob,
Just want to Thank you. This seems to work perfectly.
Once again thank you a lot.

Juan