Slow macro

[alf bryn]

I have a maco with a number of loops in it that runs a bit slowly.

Recently I read a post in this NG that said :

Macros that work on the worksheet cells are slow.
You can greatly increase speed if you set up an array of the variant type
from you worksheet cells then use code that works with the array.

As my knowledge of arrays are minute I would be gratefull for a bit of
advice how to change my macro. Part of my original macro below.

Sub cdumacro()

Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each cell In Worksheets("cdu").Range("AS56:AS102")
If cell.Value <> "E" Then
Range(cell.Value).Copy
Range(cell.Offset(0, 1).Value).PasteSpecial Paste:=xlValues
End If
Next cell


Alf

 

[Bob Phillips]

Alf,

I don't think arrays would save you much, if anything. Your data
manipulation all seems to be to the worksheet, so there is nothing to be
gained here.

That little bit of code should be lightning fast, it is only 57 cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim Thomlinson]

The code you have there is generally very efficient. Arrays will not help you
at all in this instance that I can see. Is this sub being called from a
change event procedure? That is the only thing I see that could cause speed
issues as you would be in a recursive call.

 

[Rafael Guerreiro Osorio]

Hi Alf!

I agree with Bob and Jim, but you *might* get better results if you knock
down copying/pasting and just assign the value on one cell to the other.
Guess you have two columns, AS has the address of values, and AT the address
where values must be written. If this is the case substitute everything
inside the loop by:

If cell.Value <> "E" Then _
Range(cell.Offset(0, 1).Value) = Range(cell.Value)

Best,

Rafael

 

[alf bryn]

Hi Bob, Jim and Rafael !

Thanks for your information. I guess I just got carried away by the comment
"50 times faster" and wanted to see if it was possible to speed up / inprove
my macro.

Rafael you are almost right in your analyse of my macro. The column AS holds
not a value but a range information. The values of this range I want to copy
and AT has the address the range values should be copied to.

Example the "value" of "AS56" is "cdu!AC3:AE4" and "AT56" has the address
where the values of range
"cdu!AC3:AE4" must be written.

Tried your suggestion but it did not work, got no values written to the
sheet "plan".

I think it's because the real range value in "AS56" is a formula:
=IF(C4=0;"E";AS&"AC3:AE"&AF12)

This formula gives me the range "cdu!AC3:AE4"

Still I liked your idee and I'll try somthing like
Range(Range(Cell)).Value and see if I can get it to work.

Thanks again

Alf

 

[Rafael Guerreiro Osorio]

Oi, Alf,

My suggestion works only with single cell range, which is not the case.
Then, I guess there's nothing to do, you could try to nest other FOR EACH in
the one you already have, but I don't think this would improve the speed of
your macro.

Best,

Rafael