Change code to operate quickly on slower machine

[BlastRanger]

Hi,
Below code given by Pike in this post :
http://www.excelforum.com/excel-gene...is-filled.html

Please Login or Register  to view this content.

that code can automatically copy down formula to current row.
That works fine on fast machine, but in my company there are several slow computer that IF I inject that code to an excel file, it will slow down the calculation process (when I input value on new row, Excel hang for about 5 seconds before complete the script, maybe because the code also detect the row of formula from first row to current row, not only detect from previous of current row).

Can someone help me simplify the code to operate as fast as possible on slower machine? maybe to copy down only from the previous of current row for example :
Current row is 55
just execute the VBA to copy down from previous of current row (54), not to detect those before 54 (because that will slowing all the process)

Thank You

[royUK]

You shouldn't need code to fill down formulas, all the Excel versions you list have Lists or Tables as a data feature. If you convert your data to a list or Table then formulas automatically fill down.

If calculation is a problem switch it to manual

Please Login or Register  to view this content.

[BlastRanger]

You shouldn't need code to fill down formulas, all the Excel versions you list have Lists or Tables as a data feature. If you convert your data to a list or Table then formulas automatically fill down.

That List will only copy down formula of =sum() those if, countif, etc won't copy down automatically in list, or I need tweak something to make it work?

the Code seem work for example :
A1 contain formula
I jump to A5
A5 When I enter value, process is faster.

BUT, if :
A1 contain formula
A2 Enter value, excel still hang 5 seconds before completing the script.

Confused

[BlastRanger]

bump no response

[DonkeyOte]

I've not really looked at this in any depth but whenever you use the Change Event and in turn change the sheet it follows you should be toggling Events
(else worse case: infinite loop best case: needlessly invoking the Change Event)

Please Login or Register  to view this content.

You might find if you investigate SpecialCells you will come up with a slightly more efficient approach.

[BlastRanger]

Hey it works.
It speed up 200%
Previous code hang about 5 seconds, now this code only about 1+ second.
Thank You.
Got some bug there, it will copy down formula when we enter numeric value in a new row.
But when I enter text value first, the code broken (stop to copy down anymore)

[DonkeyOte]

Got some bug there, it will copy down formula when we enter numeric value in a new row.
But when I enter text value first, the code broken (stop to copy down anymore)

I revised the code almost as soon as I posted it.
I reversed the logic of the initial test such that the App level settings were restored to prior states. It may be you were so quick that you took the initial code ?

In VBE to reinstate Events call up the Immediate Window into which type

Please Login or Register  to view this content.

Previous code hang about 5 seconds, now this code only about 1+ second.

Have you investigated SpecialCells ? Pending your set up I suspect you can improve the above - I'm afraid I've not the time to do this myself.

[BlastRanger]

As I describe before in any of my post regarding VBA.
I don't know anything about code in VB project.

So I can only hope to get the working code and paste in my document.
I won't be able to analyze it myself, even worse, I don't know from where the code comes.

Thanks

[DonkeyOte]

I don't know anything about code in VB project.

So I can only hope to get the working code and paste in my document.
I won't be able to analyze it myself, even worse, I don't know from where the code comes.

If you make no effort to learn then that is true but it is also a misguided approach which will come back to haunt you in due course...

[BlastRanger]

Well that's really inspired me to learn. In mean time, I really need this urgently, that would be impossible for me to code that now. And if learn from beginning, then I wonder how long it would be..

[BlastRanger]

Problem Solved.
Add DonkeyOte code to the end before end sub.

Please Login or Register  to view this content.

Thanks Donkey