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. | ||
Code:
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
Last edited by a moderator:
