Solved

# ignore error

Posted on 2011-10-27
161 Views
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
0
Question by:Seamus2626

LVL 15

Accepted Solution

on error resume next
0

LVL 18

Assisted Solution

You have a few options:
In each If add a new check:
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
If Sheets("Live Bloomberg Data").Range("B4").Value<>0 Then
Range("J" & i).Value = Range("I" & i).Value / Sheets("Live Bloomberg Data").Range("B4").Value
Else
'something here
End If
....

Or, you can simply add, to the top of the Sub/Function:
On Error goto errorhandler:
Then, outside the Sub/Function (usually at the end of the file):
errorhandler:
Resume Next

This one will raise the error event, but will ignore the line that caused it and resume as if it weren't there.
0

Author Closing Comment

Thanks guys!
0

## Join & Write a Comment Already a member? Login.

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

#### 754 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!