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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
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) |