Excel VBA - Unique List - Type Mismatch

1
votes

I'm getting a type mismatch error when I try to run this code on opening the workbook, the line highlighted by the debugger is the 2nd to last, I've added a comment to the code so you know where.

The line where there is an error is the same as a line of code further up so I'm unsure why I get a type mismatch error following the second loop.

I have tested the two loops separately in their own modules and it works fine. It's when I combine them into 1 module and try to run on opening the workbook that I get the error.

Private Sub Workbook_Open()

Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")

Set InputRng = Worksheets("AA").Range("C2:AF366")
Set OutRng = Worksheets("Unique Lists").Range("A2")

For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next

OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)

Application.CutCopyMode = False

Set dt = CreateObject("Scripting.Dictionary")
Set InputRng = Worksheets("CT").Range("C2:AF366")
Set OutRng = Worksheets("Unique Lists").Range("B2")

For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
'ERROR OCCURS ON THE NEXT LINE
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)

End Sub

For info: the code is designed to create 2 unique lists from cell ranges on different worksheets upon opening the workbook.

2
vbaexcel
Are the values your store into the second dictionary long (more the 255 chars)? - R3uK
Nope, they're names of people - megatron77
In the most friendly manner - why don't you write Option Explicit on top of your code, try to compile it and then post it again? msdn.microsoft.com/en-us/library/y9341s4f.aspx - Vityata
@PaulG has the answer, the second dictionary was empy. Thanks - megatron77
@R3uK - I tried to debug and noticed that it is not full, then saw your question as well. - Vityata

2 Answers

1
votes

Make sure you have data in your "CT" worksheet. If all of your cells inside Range("C2:AF366") don't have any values, then dt.Count = 0 (since your Dictionary is Empty), and this will result with a run-time error.

You already defined and set OutRng, in Set OutRng = Worksheets("Unique Lists").Range("B2"), so in your error line you can use:

OutRng.Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
0
votes

That's one way to debug dictionary, when you are not sure what do you have inside- take a look at the last lines:

Option Explicit

Public Sub TestMe()

    Dim rng As Range
    Dim InputRng As Range, OutRng As Range
    Dim dt As Object
    Set dt = CreateObject("Scripting.Dictionary")

    Set InputRng = Worksheets("AA").Range("C2:AF366")
    Set OutRng = Worksheets("Unique Lists").Range("A2")

    For Each rng In InputRng
        If rng.Value <> "" Then
            dt(rng.Value) = ""
        End If
    Next

    OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.keys)

    Application.CutCopyMode = False

    Set dt = CreateObject("Scripting.Dictionary")
    Set InputRng = Worksheets("CT").Range("C2:AF366")
    Set OutRng = Worksheets("Unique Lists").Range("B2")

    For Each rng In InputRng
        If rng.Value <> "" Then
            dt(rng.Value) = ""
        End If
    Next

    'ERROR OCCURS ON THE NEXT LINE
    OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.keys)

    Dim dtKey As Variant

    For Each dtKey In dt.keys
        Debug.Print dtKey
    Next dtKey

End Sub

Indeed, your code works quite ok when dt.Count is not 0.