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 decided to use an intermediate procedure that will 1st pass the values from each sheet into separate arrays using simple loops. I then used those arrays in a Boolean Loop procedure to populate the final output array. The Intermediate Arrays do populate completely, but The final output array comes out partially populated. My code is completely procedural because I’m a novice, but I’ve gone over the logic and simplified it 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 1st column. And, the Larger Set of stings in the 2nd sheet are arranged Horizontally in Row 1. Thanks in advance.

Example Sheet 1 of 2:

Example Sheet 2 of 2

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