Divide Expression stops in Macro when Can't divide
 

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

Divide Expression stops in Macro when Can't divide
 

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

--
Bob

"JUAN" wrote in message
...
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

Divide Expression stops in Macro when Can't divide
 

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

Divide Expression stops in Macro when Can't divide
 

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


-----Original Message-----
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

--
Bob

"JUAN" wrote in message
...
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


.

Divide Expression stops in Macro when Can't divide
 

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?

"Juan" wrote in message
...
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


-----Original Message-----
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

--
Bob

"JUAN" wrote in message
...
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


.

Divide Expression stops in Macro when Can't divide
 

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" wrote in message
...
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


-----Original Message-----
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

--
Bob

"JUAN" wrote in message
...
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


.

Divide Expression stops in Macro when Can't divide
 

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

Juan
-----Original Message-----
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" wrote in message
...
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


-----Original Message-----
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

--
Bob

"JUAN" wrote in message
...
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


.



.