Scripting Dictionary for Sumif Statement

**sriley5** · 03-09-2020, 03:31 PM

Hi,

I have a large data set and using a sumif formula take a while (4-5 Minutes) is there any chance this can be done via a scripting dictionary?

**gue2013** · 03-09-2020, 04:30 PM

I added the macro to your file. I added a reference to "Microsoft Scriping Runtime"

Sub CollectData()
  
  Dim dict As New Dictionary
  Dim i As Long
  
  Dim vArr As Variant
  Dim Keys As Variant
  
  'read data into variant array
  vArr = ActiveSheet.Range("F1").CurrentRegion
  
  ' collect data below header till end
  For i = 2 To UBound(vArr, 1)
    If dict.Exists(CStr(vArr(i, 1))) Then
      dict.Item(CStr(vArr(i, 1))) = dict.Item(CStr(vArr(i, 1))) + CInt(vArr(i, 3))
    Else
      dict.Add CStr(vArr(i, 1)), CInt(vArr(i, 3))
    End If
  Next
    
 '--- test output ---
 ' copy summary data to sheet
  i = 5
  For Each Keys In dict.Keys
    i = i + 1
    ActiveSheet.Cells(i, 1) = Keys
    ActiveSheet.Cells(i, 2) = dict.Item(CStr(Keys))
  Next

End Sub

**sriley5** · 03-10-2020, 11:40 AM

Gue2013 - I get a user defined error when trying to declare the new Dictionary

**maras_mak** · 03-09-2020, 04:46 PM

Check this code.

Sub Sum_()
    Dim a()
    Dim i As Long, lr As Long
    Dim d As Object
    
    Set d = VBA.CreateObject("scripting.dictionary")
    With Sheets("Sheet1")
        lr = .Cells(Rows.Count, "F").End(3).Row
        .[A1].CurrentRegion.Offset(1, 0).ClearContents
        If lr > 1 Then
            a = .Range("F2:H" & lr).Value
            For i = 1 To UBound(a)
                d(a(i, 1)) = IIf(IsEmpty(d(a(i, 1))), 0, d.Item(a(i, 1))) + a(i, 3)
            Next
            With .[A1].CurrentRegion.Offset(1, 0)
                .Resize(d.Count, 1).Value = Application.Transpose(d.keys)
                .Offset(0, 1).Resize(d.Count, 1) = Application.Transpose(d.items)
            End With
        End If
    End With
    Set d = Nothing
End Sub

**sriley5** · 03-10-2020, 11:40 AM

Maras_Mak - Works like a charm! Thank you!

**gue2013** · 03-10-2020, 01:49 PM

you most probably forgot to add the reference to "Microsoft Scriping Runtime"

Scripting Dictionary for Sumif Statement

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Scripting Dictionary for Sumif Statement

Re: Scripting Dictionary for Sumif Statement

Re: Scripting Dictionary for Sumif Statement

Re: Scripting Dictionary for Sumif Statement

Re: Scripting Dictionary for Sumif Statement

Re: Scripting Dictionary for Sumif Statement

Thread Information

Users Browsing this Thread

Similar Threads

scripting dictionary

[SOLVED] Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function

Scripting dictionary help

Scripting dictionary help

need help with scripting dictionary

[SOLVED] Using Dictionary in VBA scripting

[SOLVED] Scripting Dictionary

Bookmarks

Bookmarks

Posting Permissions