Hello everyone
I have this code that works well but took a long time with large data
Sub Test()
Dim LR As Long, LRData As Long
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
LR = Sheets("Balance").Cells(Rows.Count, "B").End(xlUp).Row
LRData = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
With Sheets("Balance")
With .Range("E8:E" & LR)
.Formula = "=SUMIFS(Data!R5C8:R" & LRData & "C8,Data!R5C1:R" & LRData & "C1,"">=""&Balance!R3C2,Data!R5C1:R" & LRData & "C1,""<=""&Balance!R4C2,Data!R5C2:R" & LRData & "C2,Balance!RC[-3])"
.Value = .Value
End With
With .Range("F8:F" & LR)
.Formula = "=SUMIFS(Data!R5C9:R" & LRData & "C9,Data!R5C1:R" & LRData & "C1,"">=""&Balance!R3C2,Data!R5C1:R" & LRData & "C1,""<=""&Balance!R4C2,Data!R5C2:R" & LRData & "C2,Balance!RC[-4])"
.Value = .Value
End With
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Any ideas how to make it faster?
Bookmarks