Run-time error created when copied macro is run on a new workbook

Question

I have a macro which works perfectly well on one workbook, but is throwing 'Run-time error 13 type mismatch' when I have copied it to a new workbook to be used.

The macro's function is to read a list of values and create a new list, excluding strings that have been specified in the code.
The data set I'm trying to work with is located in B96:B110, and I need the results to be placed in a list starting in cell C96.

So far, the only parts of code I've changed is the name of the worksheet, the range reference of the original list and the locations of where the results needs to go.

Option Explicit

Private Sub compileList()


Dim arr(), i As Long, list As Object
Dim exclusions(), found As Boolean, j As Long

exclusions = Array("examplestring1", "examplestring2", "(examplestring3)", "Example String 4")

With Worksheets("somemacros")

    arr = .Range("B96:B110" & .Cells(.Rows.count, "B").End(xlUp).Row).Value


    Set list = CreateObject("System.Collections.ArrayList")
    For i = LBound(arr, 1) To UBound(arr, 1)
        found = False
        With list
            For j = LBound(exclusions) To UBound(exclusions)
                If InStr(arr(i, 1), exclusions(j)) > 0 Then
                   found = True
                   Exit For
                End If
            Next
            If Not found Then .Add arr(i, 1)
        End With
    Next i

    .Range("C96").Resize(list.count, 1) = Application.WorksheetFunction.Transpose(list.ToArray)

End With

End Sub

In the original workbook the code works fine, however when I try to execute the code in a new workbook the error message appears and is referring to the following piece of code:

Application.WorksheetFunction.Transpose(list.ToArray)

I've copied several macros to different workbooks before with no issues and from what I can understand a type mismatch error appears when data-types aren't matched correctly, I'm totally lost on how a mismatch has been created here, so if anyone could explain to this rookie the reason behind this issue so he could avoid this in the future, it would be hugely appreciated.

If I've left out any important information please feel free to let me know.

Many thanks

This is more of an "Did you turn it on and back off again" type question, but is that Application.WorksheetFunction.Transpose... line actually appearing on a new line under your .Range("C96")... line? I mean... I can't imagine it is since that would cause a compile error, but it's the most obvious thing wrong with the code that I can see. — JNevill
The .Transpose function can error out if the array or Range being transposed is too large. This line arr = .Range("B96:B110" & .Cells(.Rows.count, "B").End(xlUp).Row).Value looks questionable to me because if the range is B96:B110 then why is the row number of the last line also being appended to the end? If the last row is 110, then the line is actually saying arr = .Range("B96:B110110).Value; which makes arr really large. — Mistella
No it's just showing that way on here, in the editor in excel it's one line — user3014192
If I'm not mistaken shouldn't this read .Range("C96").Resize(2, list.count)? since you are transposing the resulting 2 dimensional array from your list? The second dimension is the 2 rows and the list.count here would be the count of columns needed to hold the results. (for the record, I don't think this is causing the error, necessarily, and if it is, it's sort of indirectly causing it). — JNevill
@user3014192 Cool. I figured as much. I've updated the question to reflect so we don't get all hung up on that. — JNevill

Mistella Mistella · Accepted Answer · 2019-02-08T14:48:15

One problem is the following line:

arr = .Range("B96:B110" & .Cells(.Rows.count, "B").End(xlUp).Row).Value

If the desired range is a hardcoded value of B96:B110, then the following code can be used:

arr = .Range("B96:B110").Value

If the desired range should start at B96 and go to the last line with data, then use:

arr = .Range("B96:B" & .Cells(.Rows.count, "B").End(xlUp).Row).Value

Note that the change is in the hardcoded string. Since there is a function finding the appropriate row number, there is no need to include it in the literal string.

Explanation

This section of code: .Cells(.Rows.count, "B").End(xlUp).Row, finds the last row with data in column B. If that last row is 2 than it returns a 2, if 3 then returns 3 etc.

When that code is used like so:

arr = .Range("B96:B110" & .Cells(.Rows.count, "B").End(xlUp).Row).Value

It's appending the last row number onto the end of the string address. For example, if the last row with data is 2, then the range is looking from cells B96 through B1102. If the last row is 97, than the final range being looked at is B96 through B11097 (11 thousand and 97). Assuming the last row with data in your sheet is at least in the hundreds, the final range would be from B96 through at least B110100 (that's over one hundred and ten thousand, over a tenth of a million).

That's a lot of cells leading to a very large array. No wonder the .Transpose function is having issues.

Run-time error created when copied macro is run on a new workbook

1 Answers