• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
1 Solution
 
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
 
jppintoCommented:
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
 
Seamus2626Author Commented:
Cheers Jppinto!

Seamus
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now