Type mismatch runtime error 13

Experts,

I am in the process of writing VB script to compare data in 2 worksheets and report the differences in the third worksheet. I am using Scripting Dictinary to store the data for the range in sheet1 and another Dictionary for Sheet2 data.

Everything works fine if my Sheet1 and Sheet2 have simple strings in each cell, but the moment I have a big string, it chokes with the Type mismatch error.

Example, I have the below string in one of the cells, which I am trying to compare between 2 sheets:

{CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, BOTH_SUPPRESSED, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, NORMAL, CONTINUOUS_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, NORMAL, DEMAND_ONLY, DEMAND_ONLY, NORMAL, NORMAL, DEMAND_ONLY, DEMAND_ONLY, NORMAL, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, DEMAND_ONLY, NORMAL, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, BOTH_SUPPRESSED, BOTH_SUPPRESSED, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, BOTH_SUPPRESSED, BOTH_SUPPRESSED, CONTINUOUS_ONLY, NORMAL, CONTINUOUS_ONLY, NORMAL, BOTH_SUPPRESSED, CONTINUOUS_ONLY, NORMAL, NORMAL, CONTINUOUS_ONLY, NORMAL, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, NORMAL, NORMAL, BOTH_SUPPRESSED, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, BOTH_SUPPRESSED, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, BOTH_SUPPRESSED, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, NORMAL, CONTINUOUS_ONLY, BOTH_SUPPRESSED, CONTINUOUS_ONLY, CONTINUOUS_ONLY, BOTH_SUPPRESSED, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, NORMAL, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, NORMAL, NORMAL, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, DEMAND_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, DEMAND_ONLY, DEMAND_ONLY, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, BOTH_SUPPRESSED, BOTH_SUPPRESSED, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, CONTINUOUS_ONLY, NORMAL, NORMAL, BOTH_SUPPRESSED, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, CONTINUOUS_ONLY, CONTINUOUS_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, NORMAL, DEMAND_ONLY, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, NORMAL, DEMAND_ONLY, DEMAND_ONLY, NORMAL, NORMAL, DEMAND_ONLY, DEMAND_ONLY, NORMAL, NORMAL, NORMAL, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED, BOTH_SUPPRESSED}


I have defined the Scripting Dictionary as shown below:

Dim ObjDic1 As Object
Dim ObjDic2 As Object
Dim ObjDic11 As Object
Dim ObjDic22 As Object

Set ObjDic1 = CreateObject("Scripting.Dictionary")
Set ObjDic11 = CreateObject("Scripting.Dictionary")
Set ObjDic2 = CreateObject("Scripting.Dictionary")
Set ObjDic22 = CreateObject("Scripting.Dictionary")


I have some logic to assign data from one Scripting Dictionary to another to flter out the unwanted data as part of my comparison:

For Each G In ObjDic11
If Not (ObjDic22.exists(G)) Then
ObjDic2.Item(Application.Index(ObjDic11.Item(G), 1, 1)) = ObjDic11.Item(G) ==> It fails over here on this line with Type mismatch runtime error 13 ObjDic11.Remove (G)
End If
Next G


Appreciate if anyone can help me in fixing this problem? Is there any limitation on the string length for comparing? What is the workaround for this?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)