Run-time error '13': Type mismatch

[Mike_CS]

Hi All

I'm back begging for help again.

Keep getting run-time error message when i get to the "convert values to a vertical array" part below in Red.

Can anybody see where i'm going wrong please. it should be making a list of request numbers for a combobox in a userform so i can update existing records but having many troubles with my first plan to get a list of request numbers which i can then use to populate other things with a vlook or similar.

Worksheets("datastore") = the tab in my book
Range("datastable[RqNo]") = the table in my tab with RqNo being the table column name

Option Explicit


Sub populate_reqNo_combo()
    Dim SourceWB As Workbook
    Dim ListItems As Variant
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    With FrmUpdateRequest.cmbFindReqUR


        .Clear    ' remove existing entries from the combobox


        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open(myfilepath\myfilename, _
                                      False, True)
                                      
        'no need to use all rows if empty
        ListItems = SourceWB.Worksheets("datastore").Range("datastable[RqNo]").End(xlUp).Value
        
        ' get the values you want
        SourceWB.Close False    ' close the source workbook without saving changes


        Set SourceWB = Nothing
        ListItems = Application.WorksheetFunction.Transpose(ListItems)
        
        ' convert values to a vertical array
[COLOR=#ff0000][B]        For i = 1 To UBound(ListItems)[/B][/COLOR]
            .AddItem ListItems(i)    ' populate the listbox
        Next i


        .ListIndex = -1    ' no items selected, set to 0 to select the first item


    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Jerry Sullivan]

Hi Mike,

This expression will return the value of the top cell in that field (if there are no blanks).

SourceWB.Worksheets("datastore").Range("datastable[RqNo]").End(xlUp).Value

That's probably your header, so the type mismatch error happens when Excel tries to evaluate this expression where ListItems="RqNo"

UBound(ListItems)

If you want to get the entire field, try using the .DataBodyRange property.

 

[Norie]

Does it work if you use List?

With FrmUpdateRequest.cmbFindReqUR

    ' open the source workbook as ReadOnly
    Set SourceWB = Workbooks.Open(myfilepath \ myfilename, _
                                  False, True)
    'no need to use all rows if empty
    ListItems = SourceWB.Worksheets("datastore").Range("datastable[RqNo]").Value

    ' get the values you want
    SourceWB.Close False    ' close the source workbook without saving changes

    Set SourceWB = Nothing
    .List = ListItems

    .ListIndex = -1    ' no items selected, set to 0 to select the first item

End With

 

[Mike_CS]

Does it work if you use List?

With FrmUpdateRequest.cmbFindReqUR

    ' open the source workbook as ReadOnly
    Set SourceWB = Workbooks.Open(myfilepath \ myfilename, _
                                  False, True)
    'no need to use all rows if empty
    ListItems = SourceWB.Worksheets("datastore").Range("datastable[RqNo]").Value

    ' get the values you want
    SourceWB.Close False    ' close the source workbook without saving changes

    Set SourceWB = Nothing
    .List = ListItems

    .ListIndex = -1    ' no items selected, set to 0 to select the first item

End With

Hi Norie

Yours worked a treat. Thanks very much.

Have probably gone the wrong way on this as I'm new to the deeper bits of VBA but will be combining this with a much larger code to update the record selected using the FrmUpdateRequest.cmbFindReqUR combobox displaying a few key columns to update records in my table.

Thanks again.

No doubt will be back on here in a few days looking for the answer to updating a record in an external table ha.

Much love

Mike

 

[Mike_CS]

Hi Mike,

This expression will return the value of the top cell in that field (if there are no blanks).

SourceWB.Worksheets("datastore").Range("datastable[RqNo]").End(xlUp).Value

That's probably your header, so the type mismatch error happens when Excel tries to evaluate this expression where ListItems="RqNo"

UBound(ListItems)

If you want to get the entire field, try using the .DataBodyRange property.

Hi Jerry

Unfortunately I received an runtime error 19 I think it was.

But the .DataBodyRange is something I've never seen before so I'm 'made up' (you may say delighted) that I know another piece of handy code.

You may see me again in a few days as this combobox will be populated with a request number list which I hope I can use to call up the corresponding record to update from my external table. An I haven't got a clue where to start.

Google be good today ha

Thanks again Jerry