Completly Lost now

[RobWulf]

Okay, I tried to do this myself, but I am completly new to Excel VBA coding and what my supervisor wants me to do is way beyond what I can do on my own, so I think I may be trying to eat the whole elephant </SPAN>in one bite.


I am trying to do is compare to lists of names in the same workbook

Sheet 1 has all the names for each employee in a cell range (A:A). Sheet 2 has the ticket info with the names of anyone who worked on it in a ";" sperated list for example john smith;guy thomas in another cell range (B:B)

What I want to do is have a function I can add in =timeson() that will count each time a guys name shows up and prints the number in the cell the function is entered in an empty cell.

 

[s hal]

Not sure why this is vba.. You want do count the number of times something occurs. There is a formula called COUNTIF() and (in 2007) COUNTIFS() that do these already pretty handily. I would personally suggest just doing this as a pivot table of sheet 2 with a count of a third field with a 1 in column C to count the number of times everything happens (you probably need to split your semi-colon delimited field for best results.

 

[RobWulf]

This is the code I have built, but so far is returning 0 as the result.

Public Function InvCount(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.Like(x, Test2, 0)
i = Test2(i, Test2, ";", vbTextCompare)
If pos = 0 Then
i = i + 1
Result(i) = x
End If
Next
End Function

 

[oldbrewer]

I am with S Hal - don't use VBA (unless this is a proficiency test for yourself)

 

[RobWulf]

This is what my Supervisor wants it to ultiamitly do

step 0 send jobtitle to algoyrthom from job title list
step 0.1 set counter to 0
step 1. go to a ticket row in employee sheet
step 2. parse employee list into an array
step 2.1 create a loop for # of employees
step 3. find employee name in ticket sheet
step 4. get job title of employee from employee sheet if does not existMsgbox "add job title" & inventor & to ticket sheet" or add automaticially to bottom ticket sheet.
step 5. increment job title counter then go to next ticket number and do step two and repeat until loop is finished
step 6 updated job title sheet with counter value and exit.

Thus why I am trying to get it to work in VBA.