Write a custom function

Question

QUESTION: in regard to my past question:

could u kindly aid with the formula to do so the name of the person I will add manually corresponding to the function.

ANSWER: salma,

With only this workbook open, go into the VBE (alt+F11) and then choose Insert=>Module

paste in this code

Public Function GetAccessID(Table As Range, Current_ProcessID As Range, Current_FunctionID As Range) Dim cpID As Range, cfID As Range, lcpID As Long, lcfID As Long Dim tb As Range, v As Variant, i As Long, s As String Set cpID = Current_ProcessID Set cfID = Current_FunctionID lcpID = cpID.Value lcfID = cfID.Value Set tb = Table v = Table cnt = UBound(v, 1) For i = 1 To cnt

If v(i, 1) = lcpID And v(i, 3) = lcfID Then
  lacID = v(i, 5)
  s = s & lacID & ","
  Debug.Print s, lacID, v(i, 1), lcpID, v(i, 3), lcfID, v(i, 1) = lcpID And v(i, 3) = lcfID
End If

Next If Len(Trim(s)) > 0 Then

  s = Left(s, Len(s) - 1)

End If GetAccessID = s End Function

then in D2 of your results sheet put in a fomrula like this formula (change the "Data" to reflect your sheet name with the data and the range where the data is located on that sheet.

=GetAccessID(Data!$A$1:$E$61,$B2,$C2) then drag fill it down column D

I understood you to say that the combination of Process and Function ID define a user name, so I didn't include that in the formula.

Tested and worked for me.

note: I didn't put any error checking in the code (ex: that the second and third arguments were single cells containing a number). Also, I set up the code to expect Process ID and function ID to be a whole number (not a text value) since that is what you image appears to portray.

---------- FOLLOW-UP ----------

QUESTION:

Answer

Salma, Must have misread your picture. A little difficult to try to write code flipping back and forth between exel and pictures. Hopefully this wil give you what you want:

Revised Usage:

=GetAccessID(Data!$A$1:$F$61,$B2,$C2)

Public Function GetAccessID(Table As Range, Current_ProcessID As Range, Current_FunctionID As Range) Dim cpID As Range, cfID As Range, lcpID As Long, lcfID As Long Dim tb As Range, v As Variant, i As Long, s As String Set cpID = Current_ProcessID Set cfID = Current_FunctionID lcpID = cpID.Value lcfID = cfID.Value Set tb = Table v = Table cnt = UBound(v, 1) For i = 1 To cnt If v(i, 1) = lcpID And v(i, 3) = lcfID Then

 lacID = v(i, 6)
 s = s & lacID & ","

End If Next If Len(Trim(s)) > 0 Then

 s = Left(s, Len(s) - 1)

End If GetAccessID = s End Function