A bit of Help with an array code

[RobWulf]

Okay, I am by no means and expert in VBA, I am just learning it for work and this is starting to make me want to pull my hair out (being bald means i have to grow it out, then yank :( )

Here is what I have so far:


Public Function EmpCount(ByRef Employees As Range) As Long
Dim j As Long
'Dim strinv As Variant
Dim slen As Long
Dim EmpList As Variant
Dim EmpNam As Variant

EmpNam = ThisWorkbook.Worksheets("Employees").Range("A:A")
EmpList = ThisWorkbook.Worksheets("2013 Tickets").Range("P:P")

Dim Test1, Test2, Result(20)
Test1 = ThisWorkbook.Worksheets("Employees").Range("A:A")
Test2 = ThisWorkbook.Worksheets("2013 Tickets").Range("P:P")
For Each x In Test1
pos = 0
On Error Resume Next
pos = Application.WorksheetFunction.Match(x, Test2, 0)
If pos = 0 Then
i = i + 1
Result(i) = x
End If
Next
End Function



However when I add the function I am getting a 0 as a result? How do I get it to register the fact that my Array from the 2013 Tickets has the names listed as Joe Smith;Jack Smith with a ";" seperating the names. I think that is what is confonding it. Aside from that I think I am really close on this, as a first timer.

[patel]

attach please a sample file for testing

[RobWulf]

Attached

[patel]

Explain better your goal, attach please a sample with more data and desired result

[snb]

If you use a function, it has to return a value of that function.


Public Function EmpCount(ByRef Employees As Range) As Long
Empcount=12
End Function