Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA validation & error reporting
My "issue" is the following:
I need to check several arrays of data for invalid data in the shape of "#DIVISION/0!" and "#"'s. I need VBA first to load the field B1 which contains the hashmark and run through a number of arrays to see if a similar invalid value is encountered. If no invalid value is found, the routine should proceed to the next invalid character represented in cell C1 and run through the whole set of arrays again. If an invalid value is encountered, the routine should preferable report where in the sheet, the invalid value is found. I am aware that this not in fact what the messagebox is doing at the moment. If I want to use names instead of ranges, will it still be possible for VBA to report the cell location containing a "corrupt" cell? Being something of a novice in the field of VBA, I can't see where my code fails. Any help would be greatly appreciated. My code follows below. Kragelund Public Sub Data_Analysis_test() Dim MyCell, MyRng, i As Integer, j As Integer, flg As Boolean, Name As Range MyCell = Array("B1", "C1") MyRng = Array("D7:G23", "D27:G41", "d45:G72", "i45:j70") For i = 0 To UBound(MyCell) For j = 0 To UBound(MyRng) Do While Application.CountIf(Range(MyRng(j)), Range(MyCell(i)).Value) = 0 Exit Do MsgBox "Check the value in cell " & MyCell(i) Next j Next i Loop End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
look-up table - or - error reporting | Excel Discussion (Misc queries) | |||
Error Reporting questions | Excel Programming | |||
Corporate Error Reporting with DW.exe | Excel Programming | |||
Error Reporting | Excel Worksheet Functions | |||
#VALUE# error with Data Validation | Excel Discussion (Misc queries) |