Overcome VBA array size limitation

[Francine Kubaka]

Hello,

I am desperate. I have a dataset which contains 15,000 rows by 25 columns
(and I don't want to switch to Access at this point). I desperately need to
perform several simple and complex calculations on this dataset and would
rather do it by VBA arrays as opposed to ranges.

For example I'd rather loop ARRAY(3, i) = ARRAY(1, i) + ARRAY(2, i) and
then pass the resulting array to the worksheet than do a thousandfold
cell.offset loop.

Help please?

K

 

[Alan Beban]

I don't understand what you're asking.

Alan Beban

 

[JohnI in Brisbane]

Francine,

There is no need to loop to do the calculation you want.
The following macro runs in an instant, by working on the whole range at
once.

================================
Sub Calculate5000RowsInstantly()
With Range("C2:C5001")
.FormulaR1C1 = "=RC[-2]+RC[-1]"
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub
===========================================

If there are other reasons why this method isn't suitable, please reply.

Regards,

JohnI

 

[Francine Kubaka]

I guess I shoud just try it...
I agree that it should be much faster than looping through individual cells.
One problem though, - several columns in "precedents" are hours and
weeknumbers, thus the "dependants" should switch accordingly, i.e.
IF(R[-1]C[-1]<>24, R[-1]C, R[-1]C+1) . I guess I should just incorporate the
switch in the range formula. And thirdly, only a few of my formulas are like
this, most of them are either GETPIVODATAs or LOOKUPs or both. Still, I
think your suggestion should speed the process up considerably.

Thanks,

Francine

Francine,

There is no need to loop to do the calculation you want.
The following macro runs in an instant, by working on the whole range at
once.

================================
Sub Calculate5000RowsInstantly()
With Range("C2:C5001")
.FormulaR1C1 = "=RC[-2]+RC[-1]"
.Copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub
===========================================

If there are other reasons why this method isn't suitable, please reply.

Regards,

JohnI

Hello,

I am desperate. I have a dataset which contains 15,000 rows by 25 columns
(and I don't want to switch to Access at this point). I desperately

need

to
perform several simple and complex calculations on this dataset and would
rather do it by VBA arrays as opposed to ranges.

For example I'd rather loop ARRAY(3, i) = ARRAY(1, i) + ARRAY(2, i) and
then pass the resulting array to the worksheet than do a thousandfold
cell.offset loop.

Help please?

K