Hi BT, thank you for the feedback & comprehensive explanation of the issue. Moving forward, a few things would help to get to the bottom of the issue. Firstly, I’ll explain what this line of code does.
(It’s easier to explain if you think of things in terms of a range).
If CDbl(ar(i, 2)) >= Start And CDbl(ar(i, 2)) <= Finish Then
The data in your table is loaded into an array (
ar) which is then looped through one
row at a time – where
i is the row number, and the number following the comma after the
i is the
column number. The value is confirmed as a Double data type via the CDbl(). Therefore, that line of code asks – is the value in the second column greater than or equal to the value stored in the variable Start
and is it less than or equal to the value stored in the variable Finish, and if so
Then this line:
.Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 4)
stores that value as a Dictionary key – and the value in column 4 of the same row ar(i,
4) as the Dictionary
item for that key –
unless that key already exists, in which case it increments the value to the value already stored in the item for that key (the equivalent of summing the value – which is what ultimately returns the Sumifs() total once the entire data has been processed.)
What would help would be if you could tell me what values you are getting for the variables when the code errors. So when the code stops, put the mouse cursor over the following variables on the highlighted line and tell me what you get for
Start &
Finish (in both cases it should be a number, something like 44382 or similar); over
ar (should appear as a date, something like 1/7/2021) and
i (should be a number – if it’s 1 then the code doesn’t like anything about that line in relation to the data, if it’s greater than 1 then whatever number it is suggests there’s a problem on that particular row in your data).
It would also help if you could provide an XL2BB sample of your actual data so I can see the true layout – you can disguise any sensitive data & it doesn’t need to be a large sample, just something similar to what you provided in post #1. Also, could you show the full actual code you used once you had made the adjustments for sheet name/cell locations etc.
I have made a small edit to the code I posted in post #3 because I wasn’t happy with the final Sort of the returned table of values.
VBA Code:
Option Explicit
Sub Faster_Than_Sumifs_V2()
Dim t As Double: t = Timer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1") '<< Change to actual sheet name
Dim Start As Double, Finish As Double
Start = CDbl(ws.Range("G2").Value2)
Finish = CDbl(ws.Range("G3").Value2)
Dim ar, i As Long, n As Long
ar = ws.Range("A2", Cells(Rows.Count, "D").End(xlUp))
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(ar, 1)
If CDbl(ar(i, 2)) >= Start And CDbl(ar(i, 2)) <= Finish Then
.Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 4)
Else
.Item(ar(i, 1)) = .Item(ar(i, 1)) + 0
End If
Next i
ar = Array(.keys, .Items)
n = .Count
End With
With ws.Range("F5").CurrentRegion
.Offset(1).ClearContents
.Offset(1).Resize(n, 2).Value2 = Application.Transpose(ar)
End With
With ws.Range("F5").CurrentRegion
.Sort Key1:=ws.Range("F6"), order1:=xlAscending, Header:=xlYes
End With
MsgBox "Completed in " & Timer - t & " seconds"
End Sub
When I ran the code, after extrapolating the data you provided down to 360K rows (bearing in mind only 1000084 had any data that falls within the date range) it took just over 1 second to turn this:
| sumifs.xlsm |
|---|
|
|---|
| A | B | C | D | E | F | G |
|---|
| 1 | Account no | Posting date | Document Type | Amount | | | |
|---|
| 2 | 1000096 | 7/01/2021 | SI | $955.00 | | Start Date | 7/05/2021 |
|---|
| 3 | 1000084 | 7/02/2021 | SI | $960.00 | | End Date | 7/12/2021 |
|---|
| 4 | 1000084 | 7/02/2021 | SI | $1,395.00 | | | |
|---|
| 5 | 1000084 | 7/02/2021 | SI | $793.95 | | Customer | Amount |
|---|
| 6 | 1000084 | 7/02/2021 | SI | $1,700.00 | | | |
|---|
| 7 | 1000084 | 7/02/2021 | SI | $375.00 | | | |
|---|
| 8 | 1000084 | 7/02/2021 | SI | $360.00 | | | |
|---|
| 9 | 1000084 | 7/02/2021 | SI | $1,235.00 | | | |
|---|
| 10 | 1000084 | 7/02/2021 | SI | $960.00 | | | |
|---|
| 11 | 1000084 | 7/09/2021 | SI | $740.35 | | | |
|---|
| 12 | 1000084 | 7/13/2021 | SI | $618.00 | | | |
|---|
| 13 | 1000084 | 7/13/2021 | SI | $1,728.00 | | | |
|---|
| 14 | 1000084 | 7/13/2021 | SI | $713.00 | | | |
|---|
| 15 | 1000084 | 7/16/2021 | SI | $424.00 | | | |
|---|
| 16 | 1000084 | 7/16/2021 | SI | $470.00 | | | |
|---|
| 17 | 1000084 | 7/16/2021 | SI | $2,610.00 | | | |
|---|
| 18 | 1000084 | 7/16/2021 | SI | $572.00 | | | |
|---|
| 19 | 1000084 | 7/16/2021 | SI | $1,055.00 | | | |
|---|
| 20 | 1000084 | 7/16/2021 | SI | $25.00 | | | |
|---|
|
|---|
Into this:
| sumifs.xlsm |
|---|
|
|---|
| A | B | C | D | E | F | G |
|---|
| 1 | Account no | Posting date | Document Type | Amount | | | |
|---|
| 2 | 1000096 | 7/01/2021 | SI | $955.00 | | Start Date | 7/05/2021 |
|---|
| 3 | 1000084 | 7/02/2021 | SI | $960.00 | | End Date | 7/12/2021 |
|---|
| 4 | 1000084 | 7/02/2021 | SI | $1,395.00 | | | |
|---|
| 5 | 1000084 | 7/02/2021 | SI | $793.95 | | Customer | Amount |
|---|
| 6 | 1000084 | 7/02/2021 | SI | $1,700.00 | | 1000052 | $0.00 |
|---|
| 7 | 1000084 | 7/02/2021 | SI | $375.00 | | 1000084 | $14,472,000.00 |
|---|
| 8 | 1000084 | 7/02/2021 | SI | $360.00 | | 1000096 | $0.00 |
|---|
| 9 | 1000084 | 7/02/2021 | SI | $1,235.00 | | | |
|---|
| 10 | 1000084 | 7/02/2021 | SI | $960.00 | | | |
|---|
| 11 | 1000084 | 7/09/2021 | SI | $740.35 | | | |
|---|
| 12 | 1000084 | 7/13/2021 | SI | $618.00 | | | |
|---|
| 13 | 1000084 | 7/13/2021 | SI | $1,728.00 | | | |
|---|
| 14 | 1000084 | 7/13/2021 | SI | $713.00 | | | |
|---|
| 15 | 1000084 | 7/16/2021 | SI | $424.00 | | | |
|---|
| 16 | 1000084 | 7/16/2021 | SI | $470.00 | | | |
|---|
| 17 | 1000084 | 7/16/2021 | SI | $2,610.00 | | | |
|---|
| 18 | 1000084 | 7/16/2021 | SI | $572.00 | | | |
|---|
| 19 | 1000084 | 7/16/2021 | SI | $1,055.00 | | | |
|---|
| 20 | 1000084 | 7/16/2021 | SI | $25.00 | | | |
|---|
|
|---|
