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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
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