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