Improve efficiency of VBA Code

Hi,
Row number 9 in this Attached example contains many formulas, These formulas will be converted to its values From row number 11 to last row of data.
Macro is running fine but it takes more time with large amounts of data.Is there a more efficient and quicker way to do?
I would appreciate any help that can be offered by way of the best approach to such a task.
please see what I have so far. Thanks in advance.

Your formula are something like this:

Please Login or Register  to view this content.

so basically if col A is blank then blank otherwise put some number or text.

Are those your actual formula? or you actually have different and more complicated formula?
If those are your actual formula, then I suggest you create a macro to put the value (100,"very good" etc) directly without a formula.
So basically:
1. sort data by col A (from the header), so the blank ones will be in the bottom.
2. use a macro to insert the number or text to the intended columns (in the rows where col A isn't blank)

Let me know if you need help.

Thanks a lot for your reply
I actually have different and more complicated formulas
The idea is to convert formulas to their values depending on many conditions through many drop-down lists and many fixed data.
Is there a way that I could speed up the running of the code.
Thanks for any help you may have

Well, turning off and on some features might help a little:

Please Login or Register  to view this content.

Thank you so much
I tried these lines but I didn't get the desired and everything is scrambled.

All help is much appreciated.

welcome back Mr. TMS
Unfortunately the same problem.I've been stuck on this for a few days.
The code is working fine on the Sample attached but as for the original data, it takes about 70 seconds.
but as I really don't know what I'm doing ... I thought I'd ask here.
Thanks a lot for your guidance

The sample data set I provided is just for illustration purposes, As for the real file, I'm not allowed to post it online.
I really hope you have some prepared macro code for this kind of problem.

Originally Posted by jonsonbero

The sample data set I provided is just for illustration purposes ...

Try this modification of your code and the above suggestions from colleagues (TMS, Akuini).
I don't think it would be faster but you can check, nothing to lose ...
Attach samples of real formulas, maybe they can be replaced with regular code.

Please Login or Register  to view this content.

According to the initial attachment a VBA demonstration without the useless (but as calculation time is execution time) :

PHP Code: 

Sub Demo1()
        Dim Rg As Range
        Application.ScreenUpdating = False
    With sheet1.UsedRange.Rows("3:" & sheet1.UsedRange.Rows.Count).Columns
        For Each Rg In .Rows(-1).SpecialCells(xlCellTypeFormulas)
           .Item(Rg.Column).Value2 = .Parent.Evaluate(Replace(Rg.Formula, "A9", .Item(1).Address))
        Next
    End With
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

First, codes well work with big data, just Excel is obviously slow …

As my post #17 procedure allocates directly the array result to each necessary column
then you must think about another array strategy without any Excel formula
depending if you want to hardcode the necessary columns or create a Settings worksheet to parameter each column …

Yes it's fast enough on my side on an old slow computer …

And as any 'full array' strategy needs exactly the same time than my post #17 procedure to write the array result to the cells,
the difference is just on the time calculation and as I yet met 'full VBA' codes running slower than procedures using Excel features …

As that also obviously depends on all we can't guess …

Originally Posted by jonsonbero

... quite long (50 -70 second) ...

Marc L's macro takes about 4 s (... "hypothetical" formulas and data, at 56 thousand rows ...)

I tested it on original data and it is ok but took some time
it seems I can't get it as a whole issue
Best and kind regards for all who shared this issue

Far not an issue as the usual Excel behavior : calculation needs some time …

Maybe if you answer to previous questions and elaborate a new strategy someone may give it a try.

I will try that

Hi,
I couldn't do anything to persuade the manager to publish the real formulas.
This is a newer version of the code but it is painfully slow.
Can anyone help with some hints regarding how to optimize the attached code to run faster?
Appreciate any help in this regards.

I have posted at this link too
http://www.vbaexpress.com/forum/show...545#post402545
Thanks for any help you may have.

As there is nothing new in your last attachment so just amend your code according to post #17 …

As this is not the same kind of formulas than your initial attachment
you just forgot to replace AC6 in the genuine formulas like for A6 …

I am sorry that I could not understand you properly, what do you mean

Just compare the Excel formula in row #6 with the formula as it must be for the needed range …

The other issue is the used range does not start in row #6 but in row #2 so you have to update the addresses in the code
or make the worksheet smart as it must be just starting in row #1, obvious and easier …

Thank you for taking time and helping me to find a solution, Although the code is easy, but it's still a bit blurry to me ... So how I can fix that?

If you have difficulty to manage Excel formulas the better is to stay with your original code as after all, it well works …

Other obvious ways is to not transform any formula like allocating directly formulas in the right places rather than above the headers
or use a Settings worksheet with each appropriate formula needed …

Is there a way to improve it ..? or what is in the code that makes the code runs slowly?

On Excel side it runs normally, no gearbox so an unique speed …

Another way for some developers is to not use any formula but hard code all the calculation using arrays …
But in case of any smallest change you will have to rewrite the procedure so it depends on your Excel / VBA skills …

Imagine the boss asking you a small change, for some reason you have only five minutes and no web access, will you be able ?
Versus your original code which does not need any mod as it uses worksheet formulas so you have five minutes to amend a formula, doable …

Last tip from obvious Excel rules since 2007 version :

use an Excel table as each time a new row is created the previous formulas are automatically added
so a VBA procedure is very not necessary …

Thanks very much Mr. Marc L for the advise and for your interest in helping me.
I feel I am idiot as a novice ....That was a little oversight on my part when I set up a smaller sample file to simulate the requirements.It's my fault.
so please accept my apologies ....This is another sample that contains your code and my code
Please run your code first to see what I mean... Thank you so much for your efforts on helping me with this topic.

As my code was based upon your initial attachment with has an unique address in all formulas
the reason why it can't work anymore with your last attachment …

There are some workarounds as yet explained previously but stay without any reaction neither any comment …

The question is why this worksheet is so badly builded ?!
How the data are created within this worksheet ?
'Cause just using an obvious Excel table - as you can see in Excel help - no code is necessary ! Nothing to do …