Need help with this Function code

Hi All,

Thanks so much for your help. You all are making my 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

Hey Man, this is real messy. First, I would create an array, and then pass it. I'm pretty sure you're just passing 4 arguments rather than 1 argument and an array into your function. Secondly, change the name of your sub and function so that they're different. Third, I would use a nested to check for values in your array versus the cell value, so that your array can be of a variable length. It seems as if this is for class, so I think it would be best if you figured out how to do this rather than having one of us give you code. See where you get with that and if you have problems from there let me know.

-B

I answered in the other thread that has the same question.