RobWulf
04-22-2013, 07:24 AM
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.
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.