Copy Paste Formulas - Any Speed Tips?

[killerkoz17]

Hi guys,

I have the following code that works well just after opening the file but gets slower and slower as the loops progress. I'm copying and pasting formulas from 'FormulaRange' to 'DestinationRange' (variable). I feel like I'm not cleaning up something, which is slowing it down. I'd like to keep it running at optimum speed the entire process. Any tips?

Please Login or Register  to view this content.

Cheers, Rob

[JOHN H. DAVIS]

Not sure if this is what you're after or whether it's much faster. Test on a copy first.

Please Login or Register  to view this content.

[killerkoz17]

Thanks for the response John. I'm actually trying to copy and paste formulas, rather than values, so this didn't work (just pasted the values in every row). It's a reasonably simple macro. I guess there are three things I'm trying to improve:

1) avoid "selecting"
2) store the formulas in the 'FormulaRange' as a single variable (if possible, to avoid copying them every loop)
3) clean up each loop to avoid storing information and clogging up speed.

All ideas appreciated. I'm using this macro a lot!

[Izandol]

What is UDF which does not calculate?

[killerkoz17]

Hey Izandol,

UDF is from the web. It's basically a LOOKUP function that finds the nth occurrence of a string in a range and returns a value relative to the nth occurrence. The UDF works fine in the worksheet. It evens works well when I do the "long" copy and paste method. But when I do the "quick" copy and paste method (i.e. FormulaRange.Copy DestinationRange) it does not calculate and returns #VALUE! (that is the paste special value). Tried DesinationRange.Calculate and I added Application.Volatile myself but they had no effect.

Please Login or Register  to view this content.

Any clues on getting this working with the quick copy and paste method would be great!

Cheers, Rob

[Izandol]

May you try:

Please Login or Register  to view this content.

[killerkoz17]

Hey Izandol,

Thanks for the reply. Unfortunately, it just copied and pasted the values (not the formulas) from 'FormulaRange' to 'DestinationRange'. I was having a look through the 'Range' object properties and methods and can't see anything that stores multiple formulas, just a single formula. But the formulas must be stored somewhere, maybe the clipboard? I'll investigate that option and reply soon.

Cheers, Rob

[JOHN H. DAVIS]

Rob:

Try changing this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

See if that does what you want?

[Izandol]

Your first code copies formulas then performs paste special - values. This is same as my code. If you do not wish values, remove the .Value = .Value line as has been suggested by John H Davis

[killerkoz17]

Thanks for the suggestions guys. Couldn't work out the clipboard methods. Tried DestinationRange.Formula = FormulaRange.Formula instead of the With block. Also removed .Value = .Value from inside the With block. Both produce the same result. The values (not the formulas) from 'FormulaRange' are entered into the 'DestinationRange'.

EDIT: This next paragraph is wrong (I've now discovered, see later posts). Range.Formula DOES work when copy and pasting multiple formulas in a range but the formulas are not relative. This method essentially copies and pastes the formulas as strings.

I've done my own testing and discovered that this method (Range.Formula) works only when copying a single formula to a range (single-to-single, single-to-multiple cells). It cannot be used to copy multiple formulas at the one time. A working alternative (I just thought of now, of course) is paste columns, rather than rows, which works fine because each column contains the same formula. Range.Formula method works well in this code:

Please Login or Register  to view this content.

I have columns that depend on different columns in different positions, so I was stuck with going down the rows. I've managed to eliminate the "selections" to come up with this, which is the best I can do I think:

Please Login or Register  to view this content.

Thanks so much for the help.

Cheers, Rob

[Izandol]

I've done my own testing and discovered that this method (Range.Formula) works only when copying a single formula to a range (single-to-single, single-to-multiple cells). It cannot be used to copy multiple formulas at the one time.

This is not correct - what was testing you did?

[killerkoz17]

This is not correct - what was testing you did?

Just playing around with my worksheet and the various suggestions in this post. It's not a difficult macro but we never got it working. Seems to me that's not what this method (Range.Formula) is designed for. As soon as I did a single formula it worked fine. Do you have a working example with multiple formulas? I'm happy to play with that and edit.

[Izandol]

Put different formulas in A1:A3 then run

Please Login or Register  to view this content.

[killerkoz17]

Put different formulas in A1:A3 then run

Please Login or Register  to view this content.

No. This does not work.

[Izandol]

This is very strange - please post example file. I have used this many many times.

[killerkoz17]

No prob. Here we go.

[Izandol]

You put formulas in A1:C1, not A1:A3. You must move formulas or change code to:

Please Login or Register  to view this content.

[killerkoz17]

You put formulas in A1:C1, not A1:A3. You must move formulas or change code to:

Please Login or Register  to view this content.

Sorry, woops! Ok, that's interesting. So, yes, you're right, the formulas do transfer (I'll edit that post above in a minute). The issue is that the formulas are not relative. It literally pastes the formulas, as if they are strings. It looks like the copy and paste method I have above is as good as it will get.

Thanks for the help,

Cheers, Rob

[Izandol]

That is correct - if you wish relative reference copy you must use FormulaR1C1 property.

[killerkoz17]

Ok. Yeah, the formulas are relative. So they would need to be entered individually using this method (FormulaR1C1)?

[Izandol]

No - this should work same way as .Formula with many cells.

[killerkoz17]

Yes! Range.FormulaR1C1 is exactly what I needed. I wish this was part of the conversation at the start! Thanks Izandol for your perseverance. A big learning curve for me, much appreciated. Here's the final code:

Please Login or Register  to view this content.

It's not maintaining the original format unfortunately, even when I use .NumberFormat (sigh) but this code is as good as it gets.

Cheers, Rob