Out of memory error VBA - Excel

Question

I'm getting an out of memory error on the following block of code. Does anyone see an obvious reason why?

Edited the code to show entire block. eventually this will loop through a directory, but until working I'll only look at one file.

Sub Get_BT_Data()
Dim fNameAndPath, data As Variant
Dim j, c, r As Integer

fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSM), *.XLSM",     Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub

Workbooks.Open Filename:=fNameAndPath

Sheets("Summary For CDP").Activate
j = Range("A2").Value
c = Range("B2").Value
data = Range("DataRay")

ThisWorkbook.Activate

r = Cells(7, 4).End(xlDown).Row

For i = 7 To r
If Cells(i, 4).Value = j Then
    If Cells(i, 4).Offset(0, 1).Value = c Then
        Cells(i, 4).Offset(0, 3).Value = data(9, 20)
        Cells(i, 4).Offset(0, 4).Value = data(22, 22)
        Cells(i, 4).Offset(0, 7).Value = data(2, 20)
        Cells(i, 4).Offset(0, 8).Value = data(15, 22)
        Cells(i, 4).Offset(0, 10).Value = data(5, 20)
        Cells(i, 4).Offset(0, 11).Value = data(18, 22)
        Cells(i, 4).Offset(0, 13).Value = data(3, 22)
        Cells(i, 4).Offset(0, 14).Value = data(16, 22)
        Cells(i, 4).Offset(0, 16).Value = data(4, 20) + data(6, 20)
        Cells(i, 4).Offset(0, 17).Value = data(17, 22) + data(19, 22)
        Cells(i, 4).Offset(0, 19).Value = data(7, 20)
        Cells(i, 4).Offset(0, 20).Value = data(20, 22)
    Else
        If i = r Then
            Cells(7, 4).End(xlDown).Offset(-2, 0).EntireRow.Insert
        Else
        End If
    End If
Else
End If
Next i

End Sub

I have r declared as an integer, the max value could will likely never exceed 400. Do I need to declare as long? — ye-olde-dev
– ye-olde-dev, Commented Nov 5, 2013 at 21:16

Sam · Accepted Answer · 2013-11-05 22:36:45Z

2

Try checking what the value of r is

changing r = Cells(7, 4).End(xlDown).Row to the below may help.

r = Cells(Rows.count, 4).End(xlUp).Row

Same for this line

Cells(7, 4).End(xlDown).Offset(-2, 0).EntireRow.Insert

Using XlDown can be dangerous as you could either miss values if you have blanks in your column, or if you have no data below row 7, then it will return the row at the bottom of the sheet. I suspect this may be happening in this case.

It's usually best practice to start at the bottom of the sheet when you're trying to find the last row of data.

Also,

You should check out how big your "DataRay" range is, try substituting it for a proper range rather than a named range,

change Range("DataRay") to something like Range("A1:E500")

If you want to check the size of your DataRay range then you could use the following at the start of your code to debug

MsgBox Range("DataRay").Rows.Count & " Rows " & Range("DataRay").Columns.Count & " Columns"

edited Nov 5, 2013 at 22:36

answered Nov 5, 2013 at 20:43

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ye-olde-dev Over a year ago

I'm passing a range to the variant array, it's 22 columns by 22 rows, I'll edit my original question with the rest of my code

ye-olde-dev Over a year ago

I tried that but it didn't work. I got the same value for r (260) using both ways. Thank you for the tip about starting from the bottom. That makes a lot of sense. I walked it through step by step and I got to i = 50 and then took the breaks off and that's when it errored. It was before the If i = r statement though.

Sam Over a year ago

@Clouse24 There is another line in your code using XlDown try altering that one as well. I didn't see that you had declared r as an integer, if you exceeded it's datasize then you would get an Overflow error, so it won't be that. Speaking of that Dim j, c, r As Integer in that statement j and c are declared as Variant. It should be Dim j as Integer, c as Integer, r As Integer

ye-olde-dev Over a year ago

gave that a shot and actually had to declare a couple of variables as long, but it worked. thank you so much for your help!

Collectives™ on Stack Overflow

Out of memory error VBA - Excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related