Array problem

[MJKelly]

Hi,
I'm having problems with the code below. I want to create an array
from a range. This array will hold the codes for different tasks.
Then i need to loop through a range and highlight any codes which are
ont contained in the array. I can do this if I manually fill the
array using code, but this takes too long and would need constant
adjustments. So filling the array from a range is far better for me.
When I run this code is stops on this line:

If c.Value = ValidationArray(i) Then
I get a subscript out of range error.

also how do I declare "i"? I've just used Dim i as variant for now

Hope you can help.
regards,
Matt


Sub ValidateStaffStatus()
'validates the task codes of the amended staff

Dim c As Range
Dim There As Boolean
Dim ErrCount As Integer
Dim i as variant
Dim ValidationArray() As Variant
ValidationArray() = ThisWorkbook.Sheets("All Shifts").Range
("B3:B41").Value

ThisWorkbook.Sheets("AWD").Select
ThisWorkbook.Sheets("AWD").Range("E2:L1001").ClearFormats
For Each c In Range("E2:E10")
If Not c.Value = "" Then 'the cell is not empty
If Not IsNumeric(c.Value) Then 'the cell value is not numeric
If Not c.Value = "NWD" Then 'not a non working day
'validate the status codes
There = False
For i = 0 To UBound(ValidationArray)
If c.Value = ValidationArray(i) Then
There = True
Exit For
End If
Next

If There = False Then
c.Interior.ColorIndex = 3
ErrCount = ErrCount + 1
End If
End If
End If
End If
Next c

If ErrCount = 0 Then
MsgBox "No errors have been found"
Else
MsgBox "Your data contains " & ErrCount & " error(s). These have been
highlighted for correction."
End If

End Sub

 

[Bob Phillips]

Try this

Sub ValidateStaffStatus()
'validates the task codes of the amended staff

Dim c As Range
Dim There As Boolean
Dim ErrCount As Integer
Dim i As Variant
Dim ValidationArray() As Variant
ValidationArray() = Application.Transpose( _
ThisWorkbook.Sheets("All Shifts").Range("B3:B41").Value)

ThisWorkbook.Sheets("AWD").Select
ThisWorkbook.Sheets("AWD").Range("E2:L1001").ClearFormats
For Each c In Range("E2:E10")
If Not c.Value = "" Then 'the cell is not empty
If Not IsNumeric(c.Value) Then 'the cell value is not numeric
If Not c.Value = "NWD" Then 'not a non working day
'validate the status codes

If Not IsError(Application.Match(c.Value,
ValidationArray, 0)) Then
c.Interior.ColorIndex = 3
ErrCount = ErrCount + 1
End If
End If
End If
End If
Next c

If ErrCount = 0 Then
MsgBox "No errors have been found"
Else
MsgBox "Your data contains " & ErrCount & _
" error(s). These have been highlighted for correction."
End If

End Sub

 

[MJKelly]

Thanks Bob. Now works a treat.
Matt

 

[Rick Rothstein]

The array that assigning a range to a Variant variable produces is a 1-based
(not zero-based) 2D array. Try changing your line to this (I didn't test
this)...

If c.Value = ValidationArray(i, 1) Then

and change your For..Next loop limits to this...

For i = 1 To UBound(ValidationArray)

although if you are ever unsure, you could always run your loop this way...

For i = LBound(ValidationArray) To UBound(ValidationArray)

and let VB figure it out. By the way, you can declare the ValidationArray
without the parentheses if you want.