mdmackillop
08-10-2017, 04:06 AM
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
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