Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Matter of Time (XL97;SR2)

    I have a spreadsheet that contains 21 columnsl by 6500 rows. Cells in each column represents a VLOOKUP to a particular range name for the column ie Col A = Range R_C1, Col B = Range R_C2 and etc. The challenge I'm having is that the calculation takes approximately 4-5 minutes (calculation set to manual; F9 to Calculate). In theory, using code should decrease the time. Unfortunately I must be doing something wrong as it takes 20 minutes; Application.ScreenUpdating is set to false and Calc is set to manual.

    The code I'm using is:
    Application.ScreenUpdating = False
    For i = 1 To 21
    On Error Resume Next
    Set wSheet = Sheets("" & i & "")
    If wSheet Is Nothing Then 'Doesn't exist
    Set wSheet = Nothing
    On Error GoTo 0
    Else 'Does exist
    WhatCol = Range("R_C" & i & "").Column
    For j = 2 To LastRow 'from row 2 to last row
    Application.StatusBar = "BUnit " & i & " Row " & j & " of " & LastRow
    Worksheets("Main").Range(Cells(j, WhatCol), Cells(j, WhatCol)).Select
    For j = 46 To 49
    Result = Application.WorksheetFunction.VLookup(Range("A" & j & ""), Range("R_" & i & ""), 5, False)
    If Result = "" Then
    Worksheets("Main").Range(Cells(j, WhatCol), Cells(j, WhatCol)).Value = 0
    Else
    Worksheets("Main").Range(Cells(j, WhatCol), Cells(j, WhatCol)).Value = Result
    End If
    Next

    Any recommendations on how to speed things up?

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Matter of Time (XL97;SR2)

    Something must have gone wrong in copying/pasting the code, for this can't work - the For j = 46 To 49 loop is nested withing the For j = 2 To LastRow loop, causing a loop index conflict, and some Next and End If instructions are missing.

    Apart from that, I don't think this would run faster than using formulas - using Application.WorksheetFunction is slower than using the equivalent function in a formula. Excel is optimized for calculating formulas, while VBA is only an interpreted language. Also, you should avoid selecting cells or ranges if it can be avoided. In the code you have posted, there seems to be no reason to select Worksheets("Main").Range(...,...).

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    John: That code should not run at all. You have three For statements and only one Next, so you should get an error before it ever starts.

    You do not DIM any of your variables which means that all of the variables are variants. That slows the code down considerably.

    Without the Next statements, it is not possible to be sure, but it looks like you also have a For loop inside a For loop where both use J as the control variable. This is going to cause all kinds of strange things to happen.

    Selecting cells on a worksheet is very slow. You have the statement below:

    <pre> Worksheets("Main").Range(Cells(j, WhatCol), Cells(j, WhatCol)).Select
    </pre>


    This statement selects a cell, but apparently for no reason, I can see where the selected cell is ever used.

    You have this code just after the first For statement:

    <pre> On Error Resume Next
    Set wSheet = Sheets("" & i & "")
    If wSheet Is Nothing Then 'Doesn't exist
    </pre>


    It is not clear what the purpose of this code is. What are Sheets named 1 through 21 and why is their existence important to running the rest of the code?

    It is very difficult to figure out exactly what this code is really trying to do, and that makes it very difficult to give advice on how to make it better. Can you upload an abreviated example workbook and an explaination of what you are trying to do?
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Matter of Time (XL97;SR2)

    Your "theory" is wrong, to reinforce what Hans alluded to:
    Formulas are MUCH MUCH faster than VB code. If you can do it with formulas, it should be done that way to speed up code. (you even have proof: 4-5 times faster, and the more calcs you do the more formulas are better.

    Other things to help speed up:
    Eliminate as many IFs as possible, use boolean formulas if possible:
    instead of:
    if(a1=5, 1,22)
    Use
    =(a1=1)*21 +1
    Same result, much faster.

    If you repeatedly use Vlookup to get many different "columns" from the same lookup, use MATCH to get a row and then use INDEX with that row to get the item. This is much faster. There are more intermediate columns/calcs, but his can speed up execution instead of constantly repeating the same calcs

    Many times a named formula can also improve performance if you are using the "same" (relative) calculation over and over again

    I also agree with Hans and Legare, what are you trying to do accomplish? Tells us what you want to do and I'm sure we can help.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    Legare,

    I must have copy/pasted the previously posted code incorrectly. Sorry for the confusion. The code is in the attached DOC file.

    Description of workbook
    1) There are 21 sheets relative to different business units. Each of the 21 sheets contains column headings-accounts, depts, .... and amounts. Each sheet is populated from another piece of code and has a unique range in it such as R_1, R_2,...R_21. Column headings are in A to E. The amount is in column E (column 5).
    2) The "Main" sheet contains all accounts, although they may not be used or listed in each of the 21 sheets previously mentioned.
    3) The "Main" sheet also contains 21 columns (each column used to do a VLOOKUP) to it's respective sheet mentioned in item 1. In using the VLOOKUP formula it takes about 4-5 minutes to calculate.
    4) By using the Sub UpdateMain I thought perhaps I would be able to reduce the amount of time in order to return the amounts. Unfortunately due to poorly structured code the actual time was in the vacinity of 20 minutes. Ugh!
    5) The variable "Result" uses the VLOOKUP function. The For j = 2 to LastRow is used pass a variable to the range ie A2, A3, ...ALastRow and the For i = 1 to 21 sets the reference to the unique range described in item 1.
    6) If the "Result" is empty the enter a zero in the appropriate cell on Sheet "Main"
    7) If the "Result" returns a value, enter the value in the appropriate cell on Sheet "Main"
    8) Overall each of the 21 columns mentioned in item 3 from row 2 to LastRow is populated with a value.

    I hope this paints a clearer picture of what I'm trying to do.

    Thanks for your assistance,
    John
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Matter of Time (XL97;SR2)

    All your code seems to do is to place the results of the Vlookup values into the cells of mainsheet, though it is tough to be entirely sure, since I don't know what the range names are exactly.

    Using Vlookup in formulas in MAIN will calculate faster than running the macro. BUT the macro will make the filesize smaller since there will be no calcs.

    What calcs do you have in the cells? It might just be a matter of 21 x 6500 vlookups will take a while.

    And if you have an IF in each one to prevent the #N/As then you double the number of Vlookups plus you have all those IFs.

    I also assume that you have INDIRECT functions to get the right sheet which will add to the calc overhead.

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    <Eliminate as many IFs as possible, use boolean formulas if possible:
    instead of:
    if(a1=5, 1,22)
    Use
    =(a1=1)*21 +1
    Same result, much faster.>

    I have read many posts referring to the demerits of using IFs and I am interested in the use of your suggested boolean formulas. Could you provide a pointer to some reference material on their construction and use? I have searched the forum without success.
    TIA,
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    I think that the boolean formula that is the same would be:

    <pre>=(a1<>5)*21 +1

    not

    =(a1=1)*21 +1
    </pre>

    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    That code makes a lot more sense. I can see a lot of things that will speed it up a little. However, without seeing the workbook it is really tough to know if there is anything that will make it any faster that using VLOOKUP on the worksheet. Using VLOOKUP in a macro to do the same thing that is being done with VLOOKUP on a worksheet will almost always be slower, not faster.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    Thanks for everyone's response.

    Legare, you're statement that using the VLOOKUP in a macro will most likely always be slower, not faster than the actual VLOOKUP on a worksheet is what I thought. Although I may be able to get the VLOOKUP macro streamlined it most likely would not reduce the 4-6 minutes via the formula in the worksheet.

    Thanks once again.
    John

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Matter of Time (XL97;SR2)

    Legare,
    Of course, you are correct.
    That's what I get for typing right before I go to bed!

    Steve

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Matter of Time (XL97;SR2)

    Rob,
    Make sure you notice Legare's correction to my formula (i had one of my occasional "brain-farts"):
    =(a1<>5)*21 +1
    is equivalent to
    =if(a1=5, 1,22)

    I have no references, It is just something I saw long time ago and use periodically.

    It works because a FALSE is treated as a value of "0" and a "TRUE" as a value of "1". Thus if you are going to get a choice of 2 numbers from an IF statement, you can generally use Boolean Math strings in place of them.

    Downsides:
    It does NOT work with STRINGS and ERRORS since they are not values
    They generally can be more "confusing" to interpret later.

    Upsides:
    Less memory used
    Faster calculations
    Can use more than 7 in a string (the limit for IFs)

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    Steve, Your response is noted with thanks. I wouldn't worry too much about the occasional "brain-fart". The time to worry is when your like me where the condition has degenerated to "brain incontinence"
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Matter of Time (XL97;SR2)

    About the only way that a macro could speed it up is if the macro could do do one lookup and then store that results in many cells that were using VLOOKUP, thus reducing the number of lookups.
    Legare Coleman

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Matter of Time (XL97;SR2)

    I don't think that would work that well in his example. The 6500 lookups are being done in 21 different sheets so every lookup appears to be unique (if I understand his setup correctly). Each lookup is only grabbing 1 value so everything seems unique

    Steve

Posting Permissions

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