Excel for Mac 365, VBA out of memory error runtime 7

Question

I am running the next VBA code:

`

`Sub ARMABASES()
'
' ARMABASES Macro
' Arma la base de inventario y producción de fianzas
'
' Acceso directo: Ctrl+Mayús+A
'

Dim MyDB As Variant
Dim MyDBP As Variant
'Dim MyData(1 To 1000000, 1 To 40) As Double
Dim Vecnames(1 To 10, 1 To 5) As String
'Dim MyMat(1 To 1368, 1 To 1525) As Double

Vecnames(1, 1) = Worksheets("DATOS").Range("B5").Value
Vecnames(1, 2) = Worksheets("DATOS").Range("B6").Value
Vecnames(1, 3) = Worksheets("DATOS").Range("B7").Value
Vecnames(1, 4) = Worksheets("DATOS").Range("B8").Value
Vecnames(1, 5) = Worksheets("DATOS").Range("B9").Value

Vecnames(2, 1) = Worksheets("DATOS").Range("B10").Value
Vecnames(2, 2) = Worksheets("DATOS").Range("B11").Value
Vecnames(3, 2) = Worksheets("DATOS").Range("B12").Value


Set SRC = Workbooks.Open(Vecnames(1, 5) & Vecnames(2, 1), 0, True, True)
Worksheets("Sheet1").Activate

  Dim lRow As Integer
  Dim lCol As Integer
  Dim rng As Range
  lRow = Range("A1048576").End(xlUp).Row
'use the lRow to help find the last column in the range
  lCol = Range("XFD" & lRow).End(xlToLeft).Column
  Set rng = Range(Cells(2, 1), Cells(lRow, lCol))
'msgbox to show us the range
'  MsgBox "Range is " & rng.Address

MyDB = rng

SRC.Close False

Worksheets("Sheet1").Activate

Range(Cells(2, 1), Cells(lRow, lCol)) = MyDB

Range(Cells(2, 11), Cells(lRow, 11)).Select

Selection.TextToColumns Destination:=Range(Cells(2, 11), Cells(lRow, 11)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yyyy;@"


Set SRC = Workbooks.Open(Vecnames(1, 5) & Vecnames(2, 2), 0, True, True)
Worksheets("Sheet1").Activate

  Dim lRowP As Integer
  Dim lColP As Integer
  Dim rngP As Range
  lRowP = Range("A1048576").End(xlUp).Row
'use the lRow to help find the last column in the range
  lColP = Range("XFD" & lRowP).End(xlToLeft).Column
 ' Set rngP = Range(Cells(2, 1), Cells(lRowP, lColP))
'msgbox to show us the range
  'MsgBox "Range is " & rngP.Address

Erase MyDB
'MyDBP = rngP
MyDBP = Range(Cells(2, 1), Cells(lRowP, lColP))


SRC.Close False

Worksheets("Hoja1").Activate
Range(Cells(2, 1), Cells(lRowP, lColP)).Value = MyDBP


Range(Cells(2, 11), Cells(lRowP, 11)).Select




End Sub

When it gets to Range(Cells(2, 1), Cells(lRowP, lColP)).Value = MyDBP appears runtime error 7 out of memory; I can't find where the problem is, the array size is not important, but it seems there is a cell where it stops.

I have store arrays with at least 500,000 records and 60 columns; this one is not that big.

I hope someone can help me

Excel for Mac 365, VBA out of memory error runtime 7

0 Answers