Running function based on multiple cell values

[march]

Hi All,

I have a column where each cell has multiple usernames (from Active Directory) separated by spaces, i.e. JSmith GBlogs MParty

I also have a function whereby if the cell only had 1 username, then I could search Active Directory for the first/lastname etc of the user.

The problem I have is that I need to be able to use this function on all usernames in the one cell and output them into the column next in the format of JSmith=JohnSmith, GBlogs=GregBlogs etc etc

I can't really separate the usernames into individual columns as there can be a hundred users per cell or only 1 (usernames from Security Groups).

Is there a way to run a For/Each type query where for each value separated by spaces I could run my function?

Is this even possible to do in Excel?

If I havent made enough sense in the explanation please let me know and I'll try explain it better (the explanation sounds alright in my head )

Thanks, much appreciated.

March

 

[Boller]

"If I havent made enough sense in the explanation please let me know"

I think it might help if you post the code you have so that it can be seen what your Function is doing.
Also, some before and after data might help.

 

[Jon von der Heyden]

Hi March

Maybe you can draw some ideas from this:

Public Sub Demo()
    Dim vararrUserName As Variant
    Dim vararrFullName As Variant
    Dim lngItem As Long
    Dim strFullName As String
    
    vararrUserName = Split(Range("A1").Text) 'load the usernames in A1 into the UserName variant array
    ReDim vararrFullName(UBound(vararrUserName)) 'redimension the FullNames variant array to the upper bound of the UserName variant array
    
    For lngItem = LBound(vararrUserName) To UBound(vararrUserName) 'loop thru each username
        strFullName = GetFullName(vararrFullName) 'your function to get the full name
        vararrFullName(lngItem) = vararrUserName(lngItem) & "=" & strFullName 'load the full name array
    Next lngItem
    
    Range("B1").Value = Join$(vararrFullName) 'write the results to B1
    
End Sub

 

[march]

Boller:

For clarification i have a number of rows in column A with data such as:
A2= JSmith GBlogs MParty (all with a space between them)
A3= CBlah

Each A cell possibly has zero records, 1 record or numerous records.

What I want is
B2= JSmith= John Smith, GBlogs= Greg Blogs etc
B3= Cblah= Craig Blah etc

Now to get the output for B, i need to run my function a few times which polls AD in the format of ("cn", A2, "firstname") for the first name and ("cn", A2, "surname")

The AD get function is called GetUserDetails.... therefore =GetUserDetails("cn", A2, "firstname")

Hope this helps.

Thanks

 

[Jon von der Heyden]

Did you at all study the code I posted?

Can you post your function code?

 

[march]

Hi Jon,

Sorry for not posting that I was working with your code, I was just trying to give Boller more information as he asked.

Cheers,