Hello the dear members of ExcelForum
I am currently working on a very big-ish sort of workbook with several bigger sheets inside it, and Iam trying to run this VB script:
Sub abc()
Const shResults As String = "Results_Sheet"
Dim ws As Worksheet
Dim a, i As Long
Set dic = CreateObject("Scripting.dictionary")
dic.comparemode = 1
For Each ws In Worksheets
With ws
If .Name <> shResults Then
a = .Range("a1").CurrentRegion.Value
For i = 2 To UBound(a)
With dic
If Not .exists(a(i, 1)) Then
.Item(a(i, 1)) = a(i, 2)
Else
.Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 2)
End If
End With
Next
End If
End With
Next
With Application
a = .Transpose(dic.keys)
b = .Transpose(dic.items)
End With
With Worksheets(shResults)
.Cells.Delete
.Range("a1:b1") = Array("Title", "Score")
.Range("a2").Resize(UBound(a)) = a
.Range("b2").Resize(UBound(b)) = b
End With
End Sub
The problem is that, when I execute this script, it throws a 'type mismatch' error, and this happens only on big files. like this one: http://xeebexa.1z.com/BIG_Sample.xlsx
What should be changed in this script, so it could support bigger files?
Thank you.
Bookmarks