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

Open in new window

Seamus2626Asked:
Who is Participating?
 
jppintoConnect With a Mentor 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
 
jppintoCommented:
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
 
Seamus2626Author Commented:
So i just replace all the Else ifs with the above? (taking into account the different references)
0
 
Seamus2626Author 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.

All Courses

From novice to tech pro — start learning today.