multiple validation

[MJKelly]

Hi,
The code below checks a range for errors. I want to build on the line
which checks the value for "AL" or "SL" to check for some 30 different
possibilities. Is there a better way of doing this than Xor c.value =
"Pm" Xor c.value = "XD" etc. Like I say this will be around 30
different possibilities.

I tried
if c.value <> ("AL", "SL", "PM") then
do something
end if

but this doesnt work. Is there a syntax I can use other than a long
winded Xor?

Sub Validate_Status()
'checks for errors in status names entries

Dim ErrCount
Dim c As Range

ThisWorkbook.Sheets("Sheet1").Select
Range("A1:A10").ClearFormats

For Each c In Range("A1:A10")
If Not c.Value = "" Then 'the cell is not empty
If Not IsNumeric(c.Value) Then 'the cell value is not numeric
'validate the staus codes
If Not c.Value = "AL" Xor c.Value = "SL" Then
c.Interior.ColorIndex = 3
ErrCount = ErrCount + 1
End If
End If
End If
Next c

MsgBox ErrCount

End Sub

Kind regards,
Matt

 

[Mike H]

Hi,


Validate against an array. Note at the beginning of your code I've created
an array of values to validate against. Extend this to your full list. In the
midddle of the code there's an If loop that validates against each element of
the array

Sub Validate_Status()
'checks for errors in status names entries
Dim MyArray()
Dim There As Boolean
Dim ErrCount
Dim c As Range
MyArray = Array("AL", "SL", "BT", "PP")
ThisWorkbook.Sheets("Sheet1").Select
Range("A1:A10").ClearFormats

For Each c In Range("A1:A10")
If Not c.Value = "" Then 'the cell is not empty
If Not IsNumeric(c.Value) Then 'the cell value is not numeric
'validate the staus codes

There = False
For i = 0 To UBound(MyArray)
If c.Value = MyArray(i) Then
There = True
Exit For
End If
Next
c.Interior.ColorIndex = 3
If There = False Then ErrCount = ErrCount + 1

End If
End If
Next c
MsgBox ErrCount
End Sub


Mike

 

[MJKelly]

Mike, that's excellent, works a treat. One further benefit for me
would be for the array to be populated with data from a range. Can
this be done?

Thanks again,
Matt

 

[Mike H]

Hi,

I've done the code seperately so you can see what's happening. The
debub.print statement is to show you the elements being printed to the
immediate window and isn't necessary in your final code. In this case the
array elements ar in column A of sheet 2.


Sub Sonic()
Dim MyArray()
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
ReDim MyArray(1 To lastrow)
For x = 1 To lastrow
MyArray(x) = Sheets("Sheet2").Cells(x, 1).Value
Debug.Print MyArray(x)
Next
End Sub

Mike

 

[MJKelly]

Mike,
Great stuff - the final piece of the puzzle.

best regards,
Matt