Need help with this Function code

[lucpian]

Hi All,

Thanks so much for your help. Your making me hand on in VBA better. I am having problem with a function I wrote which should only accept, A, B, Q as valid entries. For the purpose of reusability, I am required to use array as a parameter in the function.
I wrote the following code, but is having problem and errors.

Function MaintenanceFreq(columnname As Integer, myArray As String)
Dim rowcount
Dim R
Dim myArray(1)
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount

strVal = Sheet1.Cells(R, columnname).Value
If strVal <> myArray And strVal <> myArray Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 5
End If

Next

End Function

Sub MaintenanceFreq ()
MaintenanceFreq 17, "A", "B", "Q"
End Sub

Please, I would be very grateful if someone can help me out.

Thanks

Lucpian

[Bob Phillips]

Function MaintenanceFreq(columnname As Integer, ParamArray myArray())
Dim rowcount
Dim R As Long
Dim strVal As Variant
Dim tmpArray As Variant

tmpArray = myArray
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount

strVal = Sheet1.Cells(R, columnname).value
'For i = LBound(myArray) To UBound(myArray)
If IsError(Application.Match(strVal, tmpArray, 0)) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 5
End If
Next

End Function

Sub CallMaintenanceFreq()
MaintenanceFreq 17, "A", "B", "Q"
End Sub

[lucpian]

Thank you xld. It works