+ Reply to Thread
Results 1 to 9 of 9

Macro Needed To Speed Up A Formula

  1. #1
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Macro Needed To Speed Up A Formula

    I have a formula that works very well, the problem i have is it keeps crashing on me when i try to sort a lot of data. The formula works ok on a few hundred rows but i need it to do over 20.000 rows. Would it be possible for a Macro to do this? and to spreed things up?

    I have added the formula i use below.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Macro Needed To Speed Up A Formula

    Trying setting calculation to manual before performing your sort and then turn it back to automatic afterwards.

    Chances are your Excel hasn't really crashed, it's just struggling a bit.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro Needed To Speed Up A Formula

    Hi Dom

    Thanks for the reply, i have already done that but still getting the same thing, it just seems to crash then i get the not responding thing coming up.

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Macro Needed To Speed Up A Formula

    You shouldn't if calculation is set to manual but to be honest 20,000 sumproduct formula is probably not the best idea. When I'm dealing with that sort of volumes of complex calculation I would probably work out another way of doing what I wanted. Can you get rid of the formula once it's calculated?

    For info often when Excel says it's not responding it doesn't mean it's crashed. If you leave it long enough it will generally come back to life once it's finished calculating. You're just asking it to do one hell of a lot sorting data that contains calcs like that.

    Dom

  5. #5
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro Needed To Speed Up A Formula

    Hi Dom, i can copy the formula all the way down when it is switched to manual but it does not cope when i turn it on, i can get rid of the forumla when the calc is done if needed. When it said it was not responding i left it for over 20mins but no luck.

    I am not to sure what other ways there is of doing this calc? any ideas?

  6. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Macro Needed To Speed Up A Formula

    Pivot tables can be good for doing this sort of calc. You can use GETPIVOTDATA formula to pull information from the pivot into other data if required. Otherwise as suggested do let it calc once and then copy...paste special...values the results. You might need a faster pc

    Dom

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Macro Needed To Speed Up A Formula

    have you tried using a Pivottabel instead of sumproduct?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  8. #8
    Forum Contributor
    Join Date
    07-07-2008
    Location
    sCOTLAND
    MS-Off Ver
    Office 2003 SP3
    Posts
    256

    Re: Macro Needed To Speed Up A Formula

    Cheers guys, i have never used the GETPIVOTDATA before, I am not that sure where to begin with it but i will give it a go. Maybe a quicker PC may work lol

  9. #9
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Macro Needed To Speed Up A Formula

    The easiest way to see an example of a GETPIVOTDATA formula is to create your pivot and then in a spare cell pop an = and select one the data items in the pivot. It should give you the syntax to pull that item which you can then edit to reference cells.

    Dom

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1