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

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

VBA validation & error reporting
 

as far as i can see, you're exiting the "do" without "doing" anything.

Do While Application.CountIf(Range(MyRng(j)),Range(MyCell(i )).Value)=0
If <the cell has an error - would take me too long to
figure out how to write it exactly then
MsgBox "Check the value in cell" & MyCell(i)
Exit sub
End If
Next j
Next i
Loop
xxxxxxxxxxxxxxx
susan


Kragelund wrote:
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

VBA validation & error reporting
 

Dear Susan,

Could you elaborate on that ;)

Kragelund


"Susan" wrote:


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

VBA validation & error reporting
 

well, i'll try, but with the disclaimer that i'm far from being a
guru!!!
so, you're looking for the "#" in errors, correct? so you can use
IsError in an if statement to look for an error, instead of looking for
the hashmark.
then you want it to tell you it found an error, and where, with a
message box.
i'm not sure about using names instead of ranges, but it should work.
(some of your declarations don't seem to be corresponding to this sub,
but perhaps they are important to another area in the sub?)
anyway, here's my best shot:
xxxxxxxxxxxxxxxxxxxxxxxx
Public Sub Data_Analysis_test()

Dim MyCell, MyRng, i As Integer, j As Integer, flg As Boolean, Name As
Range
'flg & Name are not being used here......

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
For Each MyCell in MyRng
If IsError ("MyCell(i)") Then
Msgbox "Check the value in cell & MyCell(i)",
vbInformation
End If
Next j
Next i
Loop
Exit Do
End Sub
xxxxxxxxxxxxxxx
i haven't tested this & so i'm not sure if the structure/wording is
absolutely correct. but this is the idea i was thinking. i haven't
used arrays yet so i'm not sure how they differ from a range, which is
what i would use.
you'll have to do the testing & compiling & error checking :D
susan

Kragelund wrote:
Dear Susan,

Could you elaborate on that ;)

Kragelund


"Susan" wrote:


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

VBA validation & error reporting
 

You're guru enough ;) Thanks! It's not quite working yet, but I think I'm
getting there.


"Susan" wrote:

well, i'll try, but with the disclaimer that i'm far from being a
guru!!!
so, you're looking for the "#" in errors, correct? so you can use
IsError in an if statement to look for an error, instead of looking for
the hashmark.
then you want it to tell you it found an error, and where, with a
message box.
i'm not sure about using names instead of ranges, but it should work.
(some of your declarations don't seem to be corresponding to this sub,
but perhaps they are important to another area in the sub?)
anyway, here's my best shot:
xxxxxxxxxxxxxxxxxxxxxxxx
Public Sub Data_Analysis_test()

Dim MyCell, MyRng, i As Integer, j As Integer, flg As Boolean, Name As
Range
'flg & Name are not being used here......

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
For Each MyCell in MyRng
If IsError ("MyCell(i)") Then
Msgbox "Check the value in cell & MyCell(i)",
vbInformation
End If
Next j
Next i
Loop
Exit Do
End Sub
xxxxxxxxxxxxxxx
i haven't tested this & so i'm not sure if the structure/wording is
absolutely correct. but this is the idea i was thinking. i haven't
used arrays yet so i'm not sure how they differ from a range, which is
what i would use.
you'll have to do the testing & compiling & error checking :D
susan

Kragelund wrote:
Dear Susan,

Could you elaborate on that ;)

Kragelund


"Susan" wrote:


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

VBA validation & error reporting
 

why thank you, sir!
:)
can you post your final (working) solution?
thanks!
susan

Kragelund wrote:
You're guru enough ;) Thanks! It's not quite working yet, but I think I'm
getting there.


"Susan" wrote:

well, i'll try, but with the disclaimer that i'm far from being a
guru!!!
so, you're looking for the "#" in errors, correct? so you can use
IsError in an if statement to look for an error, instead of looking for
the hashmark.
then you want it to tell you it found an error, and where, with a
message box.
i'm not sure about using names instead of ranges, but it should work.
(some of your declarations don't seem to be corresponding to this sub,
but perhaps they are important to another area in the sub?)
anyway, here's my best shot:
xxxxxxxxxxxxxxxxxxxxxxxx
Public Sub Data_Analysis_test()

Dim MyCell, MyRng, i As Integer, j As Integer, flg As Boolean, Name As
Range
'flg & Name are not being used here......

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
For Each MyCell in MyRng
If IsError ("MyCell(i)") Then
Msgbox "Check the value in cell & MyCell(i)",
vbInformation
End If
Next j
Next i
Loop
Exit Do
End Sub
xxxxxxxxxxxxxxx
i haven't tested this & so i'm not sure if the structure/wording is
absolutely correct. but this is the idea i was thinking. i haven't
used arrays yet so i'm not sure how they differ from a range, which is
what i would use.
you'll have to do the testing & compiling & error checking :D
susan

Kragelund wrote:
Dear Susan,

Could you elaborate on that ;)

Kragelund


"Susan" wrote:


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