# Write a custom function

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:04 PM GMT

## 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. 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:

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