+ Reply to Thread
Results 1 to 4 of 4

Tips on performance template

  1. #1
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Tips on performance template

    Hi all,

    I recently created a template for reconcilliation.

    The problem with this template is that the calculation take so *%@#(*#%(* long!

    This is what i do:
    I paste 3 (pretty big) excel files in the template, each in different sheets.
    With formulas i calculate values which i later consolidate in 3 Pivot Tables.

    I just tried the template and it works, but it had to calculate for like half an hour and i would like to speed it up.

    Is there any way to speed this process up? Maybe with various VBA scripts?
    What are your experiences?

    Regards,
    Crispy85

  2. #2
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    365
    Posts
    328

    Re: Tips on performance template

    what sort of formulas are you using, how many are there?
    Sub Reputation()
    Dim Problem as Variant
    Dim Reputation as Integer
    For Each Problem in Forum.Threads
        If Problem.Title = "*[Solved]*" and Solver.Name = "Leon V (AW)" Then Reputation = Reputation + 1
    Next Problem
    End Sub

  3. #3
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: Tips on performance template

    He Leon,

    I'm mostly using VLOOKUP and IF(multiple in one code) formulas.

    How many is depending on the many rows in the sheet. One of them got 222000 rows, so you do the math.

    Thanks for your reply.

    Rgrds,
    Crispy

  4. #4
    Forum Contributor
    Join Date
    06-22-2011
    Location
    somerset
    MS-Off Ver
    365
    Posts
    328

    Re: Tips on performance template

    it will probably be the Vlookup. Each one has to search a range (which i'm guessing is A:A or A:D etc) so that is 222000 rows searched 222000 times.

    there are some clever tricks you can do with find in VBA if your data is in alphabetical order but it is still going to take a long time. especially if the vlookup results are in the vlookup ranges every time a vlookup field updates all the other vlookups will recalculate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sum last year's year-to-date performance as we enter this year's performance...
    By LSR1011 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 11:31 AM
  2. [SOLVED] Windows 7 :Is it possible to Disable Key tips?
    By AlanF51MS in forum Microsoft Windows Help
    Replies: 1
    Last Post: 06-11-2012, 02:28 PM
  3. Tool tips or screen tips
    By Andrew B in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2006, 02:50 AM
  4. need some tips!
    By cjjoo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-19-2005, 02:11 AM
  5. [SOLVED] Tips and Tricks
    By Cathe in forum Excel General
    Replies: 1
    Last Post: 03-17-2005, 04:06 AM

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