Array limits?

[mdmackillop]

I made up Test 1 to create data for testing and it returns #N/A from row 13751. I don't see that I've reached array size limits.
Played around further creating Test 2 and got unexpected result. Any explanations for this? Note: Test 2 takes a little time to run but try changing Step size

Sub Test1() Dim a As Long
Dim x As Long
ClearAll
Application.ScreenUpdating = False
a = 800000
ReDim arr(1 To a)
x = 100000#
For i = 1 To a Step 5
x = x + 1
arr(i) = x
arr(i + 1) = x
arr(i + 2) = x
arr(i + 3) = x
arr(i + 4) = x
Next
Cells(3, 1).Resize(a) = Application.Transpose(arr)
Cells(1, 1) = Columns(1).Find("#N/A").Row
End Sub


Sub Test2()
Dim a As Long
Dim x As Long
Dim R As Range

ClearAll
Application.ScreenUpdating = False
For a = 100000 To 1000000 Step 25000
col = col + 1
ReDim arr(1 To a)
x = 100000#
For i = 1 To a Step 5
x = x + 1
arr(i) = x
arr(i + 1) = x
arr(i + 2) = x
arr(i + 3) = x
arr(i + 4) = x
Next
Cells(4, col).Resize(a) = Application.Transpose(arr)
Cells(1, col) = a
Set c = Columns(col).Find("#N/A")
If Not (c Is Nothing) Then Cells(2, col) = c.Row
Next a

ReDim arr(1 To 1)

Cells(1, 1).CurrentRegion.Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub

Sub ClearAll()
Cells.Clear
ActiveSheet.DrawingObjects.Delete
End Sub

[SamT]

After the loop in test1, what does MsgBox i tell you?

How about MsgBox a(800000)?

[mdmackillop]

After the loop in test1, what does MsgBox i tell you? 800001
How about MsgBox arr(800000)? 260000

So info is there but not writing to spreadsheet.

[Andy Pope]

Have a read of this article
https://newtonexcelbach.wordpress.com/2016/01/01/worksheetfunction-transpose-changed-behaviour-in-excel-2013-and-2016/

[offthelip]

When I try to run your code I get type mismatch warning on this line:
win 7 excel 2007

Cells(3, 1).Resize(a) = Application.Transpose(arr)
I suspect this is where the problem is.
I tried recoding it, in the way I would do it, using my favourite variant arrays and it works with no problems.


Sub Test2()
Dim a As Long
Dim x As Long
ClearAll
Application.ScreenUpdating = False
a = 800000
' ReDim arr(1 To a)
Dim arr(1 To 800000, 1 To 1) As Variant

x = 100000#
For i = 1 To a Step 5
x = x + 1
arr(i, 1) = x
arr(i + 1, 1) = x
arr(i + 2, 1) = x
arr(i + 3, 1) = x
arr(i + 4, 1) = x
Next
Range(Cells(3, 1), Cells(a + 3, 1)) = arr
Cells(1, 1) = Columns(1).Find("#N/A").Row
End Sub

[mdmackillop]

Thanks Andy
I see the issues. Now to find the best workaround
Regards
Malcolm


Edit: I see it's just been supplied. Thanks offthelip

[SamT]

an IDEA

x = 1
For y= 16^2 to UBound(arr) Step 16^2
Paste Values Arr(x) to Arr(y)
x = x + y
Next