Problems with writing array to worksheet using resize

[Fid]

I have a two dimensional array SalesData() with the dimensions 1 to
18,888, and 1 to 66.

I am using the exact same code on another worksheet with complete
success. However the line .Range("A2").Resize(18888, 66).Value =
SalesData() gives me an Application defined or object defined error.


Sub Write_Array()
With CurrWkbk.Sheets("Data")
StatusText = "Clearing existing data on Raw_Data Worksheet"
UpdateStatusForm StatusText
.Range("A2:GA50000").ClearContents
StatusText = "Writing costed inventory data to spreadsheet to
be read for reserve processing"
UpdateStatusForm StatusText

'The following line that works in many other worksheets always
gives me an error and i can't figure out why.

.Range("A2").Resize(18888, 66).Value = SalesData()

End With

End Sub

 

[Shane Devenshire]

Hi

Is SalesData() dimensioned globally somewhere else and if so is it dimmed a
a Variant data type, I believe that is a requirement in some version of
Excel.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Fid]

SalesData is dimmed in the same module as a variant.

 

[Jon Peltier]

I wonder if there's a size limit. I've recently been working with arrays on
the order of 15k rows and <30 columns with no problem, but there's a power
of 2 between 15k and 18k. I would try adjusting the first resize parameter.
Start with 10k, then creep upwards by 2k at a time to see where it craps
out.

..Range("A2").Resize(10000, 66).Value = SalesData()
..Range("A2").Resize(12000, 66).Value = SalesData()
..Range("A2").Resize(14000, 66).Value = SalesData()
..Range("A2").Resize(16000, 66).Value = SalesData()
..Range("A2").Resize(18000, 66).Value = SalesData()

If you hit the limit, you can then split your array into two half size
pieces and dump it in two steps.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



SalesData is dimmed in the same module as a variant.