Problems with writing array to worksheet using resize
 

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

Problems with writing array to worksheet using resize
 

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" wrote in message
...
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

Problems with writing array to worksheet using resize
 

SalesData is dimmed in the same module as a variant.



On Jan 7, 2:44*am, "Shane Devenshire"
wrote:
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" wrote in message

...

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

Problems with writing array to worksheet using resize
 

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/
_______



"Fid" wrote in message
...
SalesData is dimmed in the same module as a variant.



On Jan 7, 2:44 am, "Shane Devenshire"
wrote:
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" wrote in message

...

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