# Sub error

The below code divides sums by zero, which causes an error, can anyone put in a statement that says, if cell is =0 or blank, skip division

Thanks
Seamus
``````For i = 2 To rowNum

If Range("C" & i).Value = "EU Total" Then
Range("J" & i).Value = Range("I" & i).Value * Sheets("Live Bloomberg Data").Range("B2").Value
ElseIf Range("C" & i).Value = "£ Total" Then
Range("J" & i).Value = Range("I" & i).Value * Sheets("Live Bloomberg Data").Range("B3").Value
ElseIf Range("C" & i).Value = "SF Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B4").Value
ElseIf Range("C" & i).Value = "SK Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B5").Value
ElseIf Range("C" & i).Value = "DK Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B6").Value
ElseIf Range("C" & i).Value = "NK Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B7").Value
ElseIf Range("C" & i).Value = "CK Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B8").Value
ElseIf Range("C" & i).Value = "SKK Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B9").Value
ElseIf Range("C" & i).Value = "PZ Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B10").Value
ElseIf Range("C" & i).Value = "HF Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B11").Value
ElseIf Range("C" & i).Value = "RB Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B12").Value
ElseIf Range("C" & i).Value = "TY Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B13").Value
ElseIf Range("C" & i).Value = "I# Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B14").Value
ElseIf Range("C" & i).Value = "KS Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B15").Value
ElseIf Range("C" & i).Value = "RC Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B16").Value
ElseIf Range("C" & i).Value = "MD Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B17").Value
ElseIf Range("C" & i).Value = "\$Z Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B18").Value

ElseIf Range("C" & i).Value = "\$ Total" Then
Range("J" & i).Value = Range("I" & i).Value
ElseIf Range("C" & i).Value = "\$C Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("H2").Value

ElseIf Range("C" & i).Value = "Y Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N2").Value
ElseIf Range("C" & i).Value = "\$H Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N3").Value
ElseIf Range("C" & i).Value = "\$A Total" Then
Range("J" & i).Value = Range("I" & i).Value * Sheets("Live Bloomberg Data").Range("N4").Value
ElseIf Range("C" & i).Value = "\$N Total" Then
Range("J" & i).Value = Range("I" & i).Value * Sheets("Live Bloomberg Data").Range("N5").Value
ElseIf Range("C" & i).Value = "\$S Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N6").Value
ElseIf Range("C" & i).Value = "\$T Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N7").Value
ElseIf Range("C" & i).Value = "KW Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N8").Value
ElseIf Range("C" & i).Value = "PP Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N9").Value
ElseIf Range("C" & i).Value = "IR Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N10").Value
ElseIf Range("C" & i).Value = "RU Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N11").Value
ElseIf Range("C" & i).Value = "\$M Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N13").Value
ElseIf Range("C" & i).Value = "TB Total" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("N14").Value
'if no currency is found, highlight the record
Else
If Right(Range("C" & i).Value, 5) = "Total" And Left(Range("C" & i).Value, 5) <> "Grand" Then
Range("J" & i).Interior.ColorIndex = 6
Range("C" & i).Interior.ColorIndex = 6
End If
End If

Next i
``````
Asked:
###### Who is Participating?

Commented:
After each ElseIf put like this:

If Sheets("Live Bloomberg Data").Range("B4").Value<>0 And Sheets("Live Bloomberg Data").Range("B4").Value<>"" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B4").Value
End If

jppinto
0

Commented:
Here's an example of what you can do:

ElseIf Range("C" & i).Value = "SF Total" Then
If Sheets("Live Bloomberg Data").Range("B4").Value<>0 And Sheets("Live Bloomberg Data").Range("B4").Value<>"" Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B4").Value
End If
0

Author Commented:
So i just replace all the Else ifs with the above? (taking into account the different references)
0

Author Commented:
Cheers Jppinto!

Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.