CODEWITHSUNDEEP
arraysexcelvba
GaryB
GaryB

Reputation: 3

Type mismatch error in MS Excel VBA when array has one element

First of all, please excuse any of my not technically correct terminology…

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 should be 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? I’ve searched, but have come up short on similar issues. 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

Upvotes: 0

Views: 1494

Answers (1)

user4039065
user4039065

Reputation:

Force the array if there is only one and your lbound/ubound should work in further operations.

Dim d As Variant
d = Range("rngDeptX").value2
if not isarray(d) then d = array(d)
debug.print isarray(d)

Upvotes: 3

Related Questions