VBA validation & error reporting
Kragelund wrote:
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
|