Hi Pascal and group
The error actually happened where percdiff is calculated, if the cells are empty OR if I divide by zero. The solution was to change the statement
On Error GoTo Doh
To
On error resume next.
I also moved the error handling to just before the sensitive line of code.
The solution came from the Microsoft Excel VBA Help, searching for "on error". Once the error handler has been used it can't be used again IF in the first format, but it works in the second format. That explains why it worked the first time, but not the second time...
Regards
Paul
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Pascal
Sent: Thursday, 4 July 2013 5:33 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Overflow error - unable to solve through error handling
You don't say where the error occurs in the code. so this is a guess:
The combination of the following two lines:
Dim i As Integer
i = Range("DateRow").Value
depending on what value is in the named range RangeRow, might cause a problem. Try Dimming i as Long
--- In ExcelVBA@yahoogroups.com<mailto:ExcelVBA%40yahoogroups.com>, Paul Vermeulen <paul.vermeulen@...<mailto:paul.vermeulen@...>> wrote:
>
> Hi all
>
> Below find some code that validates data entry to check whether the new value is in the same ballpark as the previous value. Stepping through the first time I encountered a blank row, and inserted an error check to cope with that.
>
> It then encountered a column where the data was "-" in both rows. I deleted the text and tried again but still receive an error 6 - Overflow error.
>
> I have tried cell formatting - verified it as "General" and even tried "Number" to try and get rid of any text remains, but to no avail. Any advice, please?
>
> Regards
>
>
>
>
> Paul
>
> Sub CheckDataEntryValidity()
>
> 'Define variables
> Dim i As Integer
> Dim percdiff As Variant
> Dim colum As Integer
>
> On Error GoTo Doh
> 'Capture active row based on date
> i = Range("DateRow").Value
>
> 'Compare value per column of active row versus previous row.
> For colum = 2 To 52
> Sheets("steel prices main").Select
>
> 'Make provision for blank rows or error values by adjusting row to compare to - use loop function.
> percdiff = Round(Cells(i, colum).Value / Cells(i - 1, colum).Value * 100, 2)
>
> 'Write results to separate sheet for reporting purposes
> Sheets("Errorsheet").Select
>
> Cells(3, colum).Value = percdiff
> 'Report any odd differences via text box.
> Doh:
>
> Next colum
>
> End Sub
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar