(Solved) – Cannot retrieve cell value within custom function

  • by
(solved)-–-cannot-retrieve-cell-value-within-custom-function
Function MergeNames(sArrDep As String, rMergeRange As Range) As String
Dim sTeamMember As String, rNames As Range
Dim iCol As Integer, sMergeNames As String, iIndex As Integer
    If rMergeRange.Rows.Count > 1 Then Exit Function
    Set rNames = Range("NAMES")
    With rMergeRange
        If sArrDep = "A" Or sArrDep = "D" Then
            iCol = .Columns.Count
                For iIndex = 1 To iCol
                Debug.Print .Cells(1, iIndex)
                    If .Cells(1, iIndex).Value = sArrDep Then
                        sTeamMember = rNames.Cells(1, iCol)
                        If sMergeNames = "" Then
                            sMergeNames = sTeamMember
                        Else
                            sMergeNames = sMergeNames & "; " & sTeamMember
                        End If
                    End If
                Next iIndex
        Else
            Exit Function
        End If
    End With
    MergeNames = sMergeNames
End Function

In the immediate window the cell value appears correctly:

? rmergerange.cells(1,iindex).value

A

However in the custom function the returned value = empty. When I run the same code as a subroutine then the content of the cell is correctly retrieved.

What am I doing wrong

Leave a Reply

Your email address will not be published. Required fields are marked *