type miss match error when filtering an array in VBA

Question

I have a simple user form in my macro-enabled Excel file, and there is a combo box (drop-down) in the form. I would like to populate the combo box options with specific header values (which are in the first row) in my Excel sheet. In my case, I have more than one hundred columns, and I just need a few of them that contain the "price" value. Below is my VBA code when the user form is initializing:

Private Sub UserForm_Initialize()
    Dim header_arr() As Variant
    Dim filtered_arr() As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet

    header_arr = ws.Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Value2

    filtered_arr = Filter(header_arr, "price") 'I am getting an error here.

    For Each item In filtered_arr 
        Me.ComboBox1.AddItem (item)
    Next
    
End Sub

note for the code:

header_arr is the array of the first row in my sheet.

filtered_arr is the array of the first row in my sheet that contains the "price" value.

but when I run the code, I get run-time error 13 (type missmatch). Any help would be appreciated.

Answer 1

Populate a Combo Box With a Row

Sub PopulateCombobox()

    Const MatchString As String = "price"
     
    Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet ' improve!

    Dim hData():
    hData = ws.Range("A1", ws.Range("A1").End(xlToRight)).Value ' or .Value2
    
    Dim sc As Long, dc As Long, HeaderString As String
    
    For sc = 1 To UBound(hData, 2)
        HeaderString = CStr(hData(1, sc))
        If InStr(1, HeaderString, MatchString, vbTextCompare) > 0 Then
            dc = dc + 1
            hData(1, dc) = hData(1, sc)
        End If
    Next sc
    
    If dc = 0 Then
        MsgBox "No headers containing """ & MatchString & """ found.", _
            vbCritical
        Exit Sub
    End If
    
    If dc < sc - 1 Then
        ReDim Preserve hData(1 To 1, 1 To dc)
    'Else ' all columns contain the match string; do nothing
    End If
    
    With Me.ComboBox1
        ' Populate column:
        .List = Application.Transpose(hData)
        ' or populate row (probably a bad idea):
        '.ColumnCount = dc
        '.List = hData
    End With
    
End Sub