Range not passing to function

[trough]

I don't normally work with Range variables but I now have a need. I wrote a function to basically rearrange a list of user names. The function works fine, however, referring to the screenshot, using the function in column E works great but trying to use it as shown in column F does not. I've searched on this to no avail and cannot figure out why the IF statement, which returns an array, won't pass to the function. It's as if the function isn't even getting called. My goal is to eliminate columns E & F so that I can generate the final list I desire in column D using a single formula that calls the function. Please advise. Thanks.

Excel 2010
	A	B	C	D	E	F
1	Smith, John	JSmith	No	-	JDoe	#VALUE!
2	Doe, John	JDoe	Yes	JDoe	SLincoln	#VALUE!
3	Jones, Mary	MJones	No	-	-	#VALUE!
4	Lincoln, Sam	SLincoln	Yes	SLincoln	-	#VALUE!
5	Carver, Tim	TCarver	No	-	-	#VALUE!
Sheet1

Cell Formulas
Range	Formula
D1:D5	{=IF(A1:A5=", ","-",IF(C1:C5="No","-",B1:B5))}
E1:E5	{=UNameList(D1:D5)}
F1:F5	{=UNameList(IF(A1:A5=", ","-",IF(C1:C5="No","-",B1:B5)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Option Explicit
Option Base 1

Function UNameList(UName As Range)
    
' declare variables
Dim MyList() As String
Dim nList As Long
Dim i As Long
Dim j As Long
    
' get list size and dimension dynamic variable
nList = UName.Count
ReDim MyList(nList)

' populate dynamic list with names
j = 0
For i = 1 To nList
 If UName.Cells(i, 1) <> "-" Then
  j = j + 1
  MyList(j) = UName.Cells(i, 1)
 End If
Next i

' populate empty variable positions
If j < nList Then
 For i = j + 1 To nList
  MyList(i) = "-"
 Next i
End If
        
' write back to workbook
UNameList = Application.Transpose(MyList)

End Function

 

[RoryA]

You aren't passing a range, you're passing an array in column F (since you pass "-" for some rows and cell values for other rows), which is why the function fails.

 

[trough]

Okay, that makes sense. However, after modifying the code, reading more on the topic, and trying several different things it's still not working so I guess I'm still clueless. Could you provide anymore hints?

And then am I passing the functions final result back to the workbook properly. I just never pass arrays back and forth so I am out of my element now. Sorry.

Excel 2010

	A	B	C	D	E
1	Smith, John	JSmith	No		#VALUE!
2	Doe, John	JDoe	Yes		#VALUE!
3	Jones, Mary	MJones	No		#VALUE!
4	,	-	No		#VALUE!
5	Carver, Tim	TCarver	Yes		#VALUE!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula E1:E5 {=UNameList(IF(A1:A5=", ","-",IF(C1:C5="No","-",B1:B5)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Option Explicit
Option Base 1

Function UNameList(UName() As String)

' declare variables
Dim MyList() As String
Dim nList As Long
Dim i As Long
Dim j As Long

' get list size and dimension dynamic variable
nList = UBound(UName)
ReDim MyList(nList)

' populate dynamic list with names
j = 0
For i = 1 To nList
 If UName.Cells(i, 1) <> "-" Then
  j = j + 1
  MyList(j) = UName.Cells(i, 1)
 End If
Next i

' populate empty variable positions
If j < nList Then
 For i = j + 1 To nList
  MyList(i) = "-"
 Next i
End If

' write back to workbook
UserList = MyList()

End Function

 

[RoryA]

Please use code tags. Thanks.

Uname is an array so it doesn’t have cells - remove the .Cells parts. Also your function isn’t returning anything since you assigned the return array to UserList rather than UNameList.

I’m on an iPad so can’t do more than hint just now.

 

[trough]

Duh! My bad. I didn't remove the .Cell when I converted to the String array instead of Range. Also, I missed the function rename I did when I was trying different things. However, I still can't get this thing to work. I put a break at the ReDim line and it doesn't stop there. It's like the function isn't even getting ran. I'm just stuck.

The array function still looks the same: {=UserList(IF(A1:A5=", ","-",IF(C1:C5="No","-",B1:B5)))}

Option Explicit
Option Base 1

Function UserList(UName() As String)

' declare variables
Dim MyList() As String
Dim nList As Long
Dim i As Long
Dim j As Long

' get list size and dimension dynamic variable
nList = UBound(UName)
ReDim MyList(nList)

' populate dynamic list with names
j = 0
For i = 1 To nList
 If UName(i) <> "-" Then
  j = j + 1
  MyList(j) = UName(i)
 End If
Next i

' populate empty variable positions
If j < nList Then
 For i = j + 1 To nList
  MyList(i) = "-"
 Next i
End If

' write back to workbook
UserList = MyList()

End Function

 

[Norie]

What is the function meant to do?

 

[trough]

That's explained in the original post I believe.

 

[Norie]

In the original post all it seems to see is 'rearrange a list of user names' and I'm afraid, for me anyway, the table/formulas/code don't explain things further.

I think you want to produce a delimited list of some sort but I may be way off.