CODEWITHSUNDEEP
excelvba
YasserKhalil
YasserKhalil

Reputation: 9538

Out of memory when populating 1d array

I have a code that calculate average for each day of the week from several worksheets through two columns, and here's the code

Sub Calculate_Averages()
    Dim e, ws As Worksheet, iYear As Integer, iRow As Integer, iCol As Integer, i As Integer, lr As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
        With ThisWorkbook.Worksheets("Adverage")
            .UsedRange.Cells.Clear
            iCol = 2: iRow = 4
            For iYear = 2022 To 2018 Step -1
                ReDim a(1 To 10, 1 To 3) As String
                Set ws = ThisWorkbook.Worksheets(CStr(iYear))
                lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
                If Not ws Is Nothing Then
                    a(1, 2) = ws.Name: a(1, 3) = ws.Name
                    a(2, 2) = "User 1": a(2, 3) = "User 2"
                    i = 0
                    For Each e In Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
                        i = i + 1
                        a(i + 2, 1) = e
                        a(i + 2, 2) = "=IFERROR(ROUND(AVERAGEIF('" & ws.Name & "'!$B$4:$B$" & lr & ",""" & e & """,'" & ws.Name & "'!$D$4:$D$" & lr & "),2),"""")"
                        a(i + 2, 3) = "=IFERROR(ROUND(AVERAGEIF('" & ws.Name & "'!$B$4:$B$" & lr & ",""" & e & """,'" & ws.Name & "'!$E$4:$E$" & lr & "),2),"""")"
                    Next e
                End If
                .Cells(iRow, iCol).Resize(UBound(a, 1), UBound(a, 2)).Formula = a
                iCol = iCol + 4
            Next iYear
        End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

I got an error Out of Memory at this line

.Cells(iRow, iCol).Resize(UBound(a, 1), UBound(a, 2)).Formula = a

Any idea how to fix such an error?

I could solve the problem partially by this line instead

ReDim a(1 To 10, 1 To 3) As String

But I got formulas as string not as formulas

Upvotes: 0

Views: 53

Answers (1)

YasserKhalil
YasserKhalil

Reputation: 9538

I could figure it out

For Each e In Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    i = i + 1
    a(i + 2, 1) = e
    v1 = Application.AverageIf(ws.Range("B4:B" & lr), e, ws.Range("D4:D" & lr))
    a(i + 2, 2) = IIf(IsError(v1), "", Application.Round(v1, 2))
    v2 = Application.AverageIf(ws.Range("B4:B" & lr), e, ws.Range("E4:E" & lr))
    a(i + 2, 3) = IIf(IsError(v2), "", Application.Round(v2, 2))
Next e

Upvotes: 1

Related Questions