I am writing a macro that copies a formula, pastes it over a range,
then copies that and pastes the values. Its working over 30,000 rows,
one column at a time. I also put a loop in to break the range up into
managable chunks based on how many characters the formula was(It would
crash often when attempting to do this all at once). Now this takes
way too long to run and is pretty unstable, is there a better way?

Here is the function that breaks up the range and copies, pastes,
copies, then pastes again. Thanks!

'Copies and pastes a formula passed as an argument (FormulaRangeName)
and pastes the value down the range(argument RangeName)

Private Sub UpdateInChunks(RangeName As String, FormulaRangeName As
String)

Dim n As Long
Dim r As Integer


Application.ScreenUpdating = False

'This If statement makes a rough approximation of complexity (by
length) and determines from that how many cells to process at a time
If Len(Sheet1.Range(FormulaRangeName).Formula) <= 30 Then

r = 100

ElseIf (30 < Len(Sheet1.Range(FormulaRangeName).Formula) <= 60)
Then

r = 80

ElseIf (60 < Len(Sheet1.Range(FormulaRangeName).Formula) <= 90)
Then

r = 60

Else

r = 40

End If



' loops through ranges of length 'r', coping and pasting the formula
then copying and pasting the values
For n = 0 To (Sheet1.Range(RangeName).Rows.Count) \ r

Sheet1.Range(FormulaRangeName).Copy

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).PasteSpecial
Paste:=xlPasteFormulas

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).Copy

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).PasteSpecial
Paste:=xlPasteValues


Next n

Application.ScreenUpdating = True


End Sub

If your formula is so complicated that its use causes XL to crash, I
would focus on cleaning up the formula, not how to continue using it
with attempts to update a worksheet in chunks!

In any case, you have a basic understanding problem with how IFs work.
Based on the stated intent the implementation of the IF statement is
flawed. If the length of a formula is 30 characters, the 1st ElseIf
will always be true.

Basically, 30<Len(x)<=60 will be true for any value of Len(x)30. Try
it with x having a length of 61.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
I am writing a macro that copies a formula, pastes it over a range,
then copies that and pastes the values. Its working over 30,000 rows,
one column at a time. I also put a loop in to break the range up into
managable chunks based on how many characters the formula was(It would
crash often when attempting to do this all at once). Now this takes
way too long to run and is pretty unstable, is there a better way?

Here is the function that breaks up the range and copies, pastes,
copies, then pastes again. Thanks!

'Copies and pastes a formula passed as an argument (FormulaRangeName)
and pastes the value down the range(argument RangeName)

Private Sub UpdateInChunks(RangeName As String, FormulaRangeName As
String)

Dim n As Long
Dim r As Integer


Application.ScreenUpdating = False

'This If statement makes a rough approximation of complexity (by
length) and determines from that how many cells to process at a time
If Len(Sheet1.Range(FormulaRangeName).Formula) <= 30 Then

r = 100

ElseIf (30 < Len(Sheet1.Range(FormulaRangeName).Formula) <= 60)
Then

r = 80

ElseIf (60 < Len(Sheet1.Range(FormulaRangeName).Formula) <= 90)
Then

r = 60

Else

r = 40

End If



' loops through ranges of length 'r', coping and pasting the formula
then copying and pasting the values
For n = 0 To (Sheet1.Range(RangeName).Rows.Count) \ r

Sheet1.Range(FormulaRangeName).Copy

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).PasteSpecial
Paste:=xlPasteFormulas

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).Copy

Sheet1.Range(Range(FormulaRangeName).Offset(r * n + 5,
0), Range(FormulaRangeName).Offset(r * (n + 1) + 4, 0)).PasteSpecial
Paste:=xlPasteValues


Next n

Application.ScreenUpdating = True


End Sub

Thanks for the reply. I didn't realize that inequalitites worked like
that in vb. I was mainly wanting to know if there is a way around
copying, pasting the formulas, copying, and pasting the values. Even
the addition of 3 cells on the same worksheet is very time consuming.
Is there a way to directly set the value?

Like I wrote previously, concentrate on the source of the problem.

Consider sharing the formula that is causing you trouble. Short of
that I don't see how I can add any more to this discussion.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Thanks for the reply. I didn't realize that inequalitites worked like
that in vb. I was mainly wanting to know if there is a way around
copying, pasting the formulas, copying, and pasting the values. Even
the addition of 3 cells on the same worksheet is very time consuming.
Is there a way to directly set the value?

Here are some of the formulas, ranging from the most simple to complex:
=O10+P10
=U10+V10+W10
=MATCH($BG$2&Amounts!$G10&Amounts!$I10,'Ultimate
Selections'!FactorTypeOutput&'Ultimate
Selections'!CoverageOutput&'Ultimate Selections'!SegmentOutput,0)
=(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)-INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1))*($BC10/(INDEX('Ultimate
Selections'!$AX$5:$BL$100,$BD10,$BG10)+(INDEX('Ult imate
Selections'!$AX$5:$BL$100,$BE10,$BG10)+(INDEX('Ult imate
Selections'!$AX$5:$BL$100,$BF10,$BG10)))))*(1/(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)/INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1)))+(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)-INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1))*($M10/INDEX('Ultimate
Selections'!$AI$5:$AW$100,$BD10,$BG10))*(1-1/(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)/INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1)))

The formulas work fine 10, 20, 30 at a time, but I need to apply them
to 30,000 rows.

For starters put the result of 2 highly repeated functions in
intermediate cells. I'm refering to INDEX('Ultimate Selections'!$E$6:
$BL$101,Amounts!$BD10-1,Amounts!$BG10,1) and INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1)

Not only will your formula be greatly simplified the overall
performance will dramatically improve since XL will not have to
repeatedly calculate the same function.

=(x-y)*($BC10/(INDEX('Ultimate Selections'!$AX$5:$BL$100,$BD10,$BG10)+
(INDEX('Ultimate Selections'!$AX$5:$BL$100,$BE10,$BG10)+(INDEX
('Ultimate Selections'!$AX$5:$BL$100,$BF10,$BG10)))))*(1/(x/y))+(x-y)*
($M10/INDEX('Ultimate Selections'!$AI$5:$AW$100,$BD10,$BG10))*(1-1/
(x/y))

You can further simplify the formulas by removing redundant parenthesis
and simple algebra (e.g., 1/x/y = y/x).

Finally, you can reduce the length of the formula by using the foll.
technique:
INDEX({range starting with A1},E1,F1)+
INDEX({range starting with A1},E1,G1)+
INDEX({range starting with A1},E1,H1)
is equivalent to the *array formula*
SUM(N(OFFSET($A$1,E1-1,F1:H1-1,1,1)))
Note that the technique uses the N() function in a long supported but
what is likely to be an undocumented manner.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Here are some of the formulas, ranging from the most simple to complex:
=O10+P10
=U10+V10+W10
=MATCH($BG$2&Amounts!$G10&Amounts!$I10,'Ultimate
Selections'!FactorTypeOutput&'Ultimate
Selections'!CoverageOutput&'Ultimate Selections'!SegmentOutput,0)
=(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)-INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1))*($BC10/(INDEX('Ultimate
Selections'!$AX$5:$BL$100,$BD10,$BG10)+(INDEX('Ult imate
Selections'!$AX$5:$BL$100,$BE10,$BG10)+(INDEX('Ult imate
Selections'!$AX$5:$BL$100,$BF10,$BG10)))))*(1/(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)/INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1)))+(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)-INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1))*($M10/INDEX('Ultimate
Selections'!$AI$5:$AW$100,$BD10,$BG10))*(1-1/(INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10,1)/INDEX('Ultimate
Selections'!$E$6:$BL$101,Amounts!$BD10-1,Amounts!$BG10+45,1)))

The formulas work fine 10, 20, 30 at a time, but I need to apply them
to 30,000 rows.