MS Excel VBA Run-time error '13': Type mismatch RRS feed

  • Question

  • First of all, please excuse any of my not technically correct usage...

    I've written a macro that cycles through departments for a selected store, creating a separate worksheet for each department, and then publishing as a PDF with each department on its own page. Depending on the store selected the number of departments can be from 1, 11, or 12. I use a named range (rngDeptX) in the Excel workbook to hold the department names, which I then use to populate an array in VBA. The process works as expected for instances of 11 or 12 departments, but I receive the Type mismatch error for the instance of 1 department.

    The relevant lines of code (I think) are below...

    The array arrSheets is used later in the process, and hasn't caused any errors. It is populated with the same values as array d, array d is the mechanism used to cycle through the department names. As stated previously, the process works as expected if the number of departments is 11 or 12. I step through the code and the expression d = Range("rngDeptX") returns the correct department when I hover over it upon receiving the Type mismatch error. Is there something different I must do if there is only one item for the array?

    Thanks for your help.

    Dim arrSheets() As String, DptCnt As Long   'array of Department names, size of array

    'determine required size of arrays, set size of arrays
    DptCnt = WorksheetFunction.CountA(Range("rngDeptX"))
    ReDim arrSheets(1 To DptCnt)

    Dim d() As Variant
    d = Range("rngDeptX")  <= this is where the error occurs

    • Edited by GaryB7 Tuesday, May 15, 2018 9:48 PM
    Tuesday, May 15, 2018 4:15 PM

All replies

  • "Is there something different I must do if there is only one item for the array?"

    Correct. Excel will return variant for Range("....") if it has more than one cell.

    But if it has one cell, it will return one of basic type. It will depend on cell's content. It may be Number/text/logical/error.

    That is Type Mismatch comes.

    One way: You can use one dummy column. Say rngDeptX uses A13:A18, you use A13:B18. while looping d, do not use d(1,2) as it will refer B1

     Hope that has no side effect on other instructions.

    Best Regards, Asadulla Javed

    Wednesday, May 16, 2018 9:01 AM