asked on

ignore error

Hi,

In the attached code, if the there is a blank cell or zero, the code errors as it is trying to divide by zero. I tried adding the below to stop this but it seems to be messing up the calculation

So instead im thinking is there a statement i can put at the start of the sub to ignroe the errors within thats sub and skip past?

Thanks
Seamus

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

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

ASKER CERTIFIED SOLUTION