Alternative to Copy and paste values in VBA

[thisguy]

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

 

[Tushar Mehta]

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

 

[thisguy]

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?

 

[Tushar Mehta]

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

 

[thisguy]

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('Ultimate
Selections'!$AX$5:$BL$100,$BE10,$BG10)+(INDEX('Ultimate
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.

 

[Tushar Mehta]

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

 

[thisguy]

Thank you very much. I truly appreciate it.

 

[Tushar Mehta]

Thank you very much. I truly appreciate it.

You are welcome. Hopefully, you will get a chance to post back about
how it works out.

--
Regards,

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

 

[thisguy]

Tushar-
Cleaned up the formulas like you recomended and they work amazingly
fast. I don't even need the loop to copy and paste anymore, I can do
them all at once. I'm an intern at an insurance comany and this is the
first time I've ever really used Excel(to actually do something) with
VBA and am just getting the hang of it. Thanks a bunch!

 

[Tushar Mehta]

Tushar-
Cleaned up the formulas like you recomended and they work amazingly
fast. I don't even need the loop to copy and paste anymore, I can do
them all at once. I'm an intern at an insurance comany and this is the
first time I've ever really used Excel(to actually do something) with
VBA and am just getting the hang of it. Thanks a bunch!

Glad you got a chance to share the result of the exercise.

It is amazing how much cleaner (and faster) a design one can get with
the judicious use of intermediate result cells. I, for one, have never
understood all those who try cramming all the world's knowledge into a
single cell. Almost like there's a global shortage of XL worksheet
cells. {grin}

--
Regards,

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