Slow macro

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

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)


"alf bryn" <alf.bryn@comhem.se> wrote in message
news:sjbIe.143241$dP1.497191@newsc.telia.net...
> 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
>
>

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.
--
HTH...

Jim Thomlinson


"alf bryn" wrote:

> 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
>
>
>

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" wrote:

> 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
>
>
>

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" <RafaelGuerreiroOsorio@discussions.microsoft.com>
wrote in message news:45E669ED-39C1-4E86-B872-77EA13388951@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>>

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

"alf bryn" wrote:

> 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" <RafaelGuerreiroOsorio@discussions.microsoft.com>
> wrote in message news:45E669ED-39C1-4E86-B872-77EA13388951@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>