Excel VBA: Element Not Being Stored in Loop

Question

The following issue has me perplexed. I have created a function to provide the column index of a particular column based on several inputs:

1. An Array (from a Range of column headers)
2. The number of data elements (Integer)
3. An Array of selected column names
4. An Array to hold the results for passage back to the sub.
5. Counters (global)

Obviously, the intention here is to pass the column indices to other functions and routines for offsetting and additional processing.

While the function itself is working, it is NOT working for the second data element. K will be recorded with appropriate index, but it won't be passed to the array. Is there something I am missing here?

Public Function getIndex(ByRef all_names As Variant, ByVal Elements As Integer, check_names() As Variant, resultindex() As Variant) As Variant
    ReDim resultindex(1 To Elements)
    For i = LBound(all_names) To UBound(all_names)
        For j = 1 To Elements
            For k = LBound(all_names, 2) To UBound(all_names, 2)
                If all_names(i, k) = check_names(j) Then                                    ' checks colName against dynamic names
                    resultindex(j) = k                                                      ' colIndex takes index of selected column
                    Debug.Print resultindex(j)
'                    k = UBound(all_names, 2)                                               ' Jump to end?
                End If
            Next k
         Next j
    Next i
End Function

Is there a particular reason why the second element is not stored? I have tried this with several different inputs and have achieved the same result. Would really appreciate anyone who's good at nested-loops to give me a nod in the right direction on this. Thanks.

Edit: it looks like it is working in the immediate window. The appropriate indices are being snagged as expected, but the second element is not being passed out.

Verifying that the values were passed:

    results(i) = getIndex(subArray(), Elements, selNames(), results())
colIndex() = results()

For i = 1 To Elements
    Debug.Print colIndex(i)
Next i
Erase result

Answer 1

You never assign a return value to your function. You also seem to be using resultindex as a ByRef parameter to hold the results. You don't need to do both.

Refactoring, try

Public Function getIndex( _
  ByRef all_names As Variant, _
  ByVal Elements As Integer, _
  check_names() As Variant) As Variant

    Dim i As Long, j As Long, k As Long
    Dim resultindex() As Variant
    ReDim resultindex(1 To Elements)
    For i = LBound(all_names) To UBound(all_names)
    For j = 1 To Elements
        For k = LBound(all_names, 2) To UBound(all_names, 2)
            If all_names(i, k) = check_names(j) Then                                    ' checks colName against dynamic names
                resultindex(j) = k                                                      ' colIndex takes index of selected column
                Debug.Print resultindex(j)
                Exit For
            End If
        Next k
    Next j, i
    getIndex = resultindex
End Function

And call it like this

results = getIndex(subArray(), Elements, selNames())

For i = 1 To Elements
    Debug.Print results(i)
Next i