Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple validation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple validation
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple validation
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple validation
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple validation
Mike,
Great stuff - the final piece of the puzzle. best regards, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple data validation | Excel Discussion (Misc queries) | |||
Multiple Data Validation | Excel Discussion (Misc queries) | |||
Multiple Data Validation | Excel Discussion (Misc queries) | |||
Validation of multiple selections | Excel Programming | |||
Multiple Validation Lists | Excel Worksheet Functions |