Populate an Array with Column Numbers that Correspond to a Set of Strings

Question

I'm trying to populate a 2-Dimensional Variant Array with a set of strings from a worksheet and their column numbers (Positions) from a larger set of strings in another worksheet.

I use an intermediate procedure that first passes the values from each sheet into separate arrays using simple loops.
I then use those arrays in a Boolean Loop procedure to populate the final output array.

The Intermediate Arrays populate completely, but the final output array comes out partially populated.

I simplified the logic as much as I can. I can only debug using the immediate window and debug.print statements.

The Reference Sheet string values (Sheet 1 of 2) are arranged vertically in the first column. The larger set of strings in the second sheet are arranged horizontally in Row 1.

Example Sheet 1 of 2:

Column A	Column B
String 1	Empty
String 2	Empty

Example Sheet 2 of 2

Column A	Column B
String 1	String 2
Empty	Empty

Sub ColumnNumberAssign()

Dim ReferenceRowCount As Integer
Dim ComparisonColumnCount As Integer

Dim RefStrings(50, 2) As Variant, ComparisonStrings(1000) As String
Dim Counter As Integer

'Column Count for Comparison Sheet
    ComparisonColumnCount = 662
    
'Row Count for reference Sheet - There 31 Strings arranged vertically in the 1st sheet column
    ReferenceRowCount = 31 

'Populating RefStrings Array with Strings Reference Excel Sheet (Sheet 1 of 2)
    Worksheets("Reference").Activate
    For i = 2 To ReferenceRowCount
        RefStrings(i, 1) = Cells(i, 1)
    Next i

'Populating ComparisonStrings Array with Strings from Comparison Excel Sheet (Sheet 2 of 2)
'There are 662 string values arranged in the 1st Sheet Row
    Worksheets("Comparison").Activate
    For i = 2 To ComparisonColumnCount
        ComparisonStrings(i) = Cells(1, i)
    Next i

'Identifying the column numbers in the Comparison Sheet for the values in the RefStrings Array
    For i = 2 To ReferenceRowCount
        For b = 2 To ComparisonColumnCount
            If RefStrings(i, 1) = ComparisonStrings(b) Then RefStrings(i, 2) = b
        Next b
    Next i

'Debugging: Making Sure RefStrings Array is completely populated (*** Failed ***)
    For i = 1 To ReferenceRowCount
        Debug.Print RefStrings(i, 1), RefStrings(i, 2)
    Next i

End Sub

Answer 1

Some suggestions for increasing chances of matches:

Sub ColumnNumberAssign()

    Dim refs As Variant, comps As Variant, wsRef As Worksheet, wsComp As Worksheet
    Dim a As Long, b As Long, col As Variant, m, rngHeaders As Range, s As String
    
    Set wsRef = ThisWorkbook.Worksheets("Reference")
    Set wsComp = ThisWorkbook.Worksheets("Comparison")
    
    'including colB here so we have a "two-column" array...
    refs = wsRef.Range("A2:B" & wsRef.Cells(Rows.Count, "A").End(xlUp).Row).Value
    'get the comparison values from row 1
    comps = wsComp.Range("A1", wsComp.Cells(1, Columns.Count).End(xlToLeft)).Value
    
    'loop `refs` and and populate the second "column" from any `comps` matches
    For a = 1 To UBound(refs, 1)
        s = UCase(Trim(refs(a, 1)))   'trim off any spaces and upper-case
        col = Empty                   'reset this
        For b = 1 To UBound(comps, 2) 'looping second (column) dimension...
            If s = UCase(Trim(comps(1, b))) Then
                col = b  'matched column position
                Exit For 'done checking
            End If
        Next b
        refs(a, 2) = col 'Empty, or column position if got match
    Next a
    
    'drop array onto worksheet for review
    With ThisWorkbook.Worksheets("Temp").Range("A1")
        .Resize(UBound(refs, 1), UBound(refs, 2)).Value = refs
    End With
    
End Sub