+ Reply to Thread
Results 1 to 85 of 85

Macro Run time to slow

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Macro Run time to slow

    I have a macro built that runs just fine but it is super slow. All the macro does is input a series of formulas into a row and then copies those formulas down the entire data set. The speed problem is likely linked to the fact that these formulas have a massive database they are looking across. So I was wondering if there was a way to get the same info in the same spots using a different macro that did not require the same amount of processing time it takes to run those formulas. Ideally I would like a macro that can do this in a matter of minutes or faster but anything faster than the 10+ hours it takes right now. I am currently using Excel 07. For reference I am going to provide the size of each of the named ranges hoping that helps you understand the problem.

    Named Ranges:

    Customerdata = Range("A1:AD558546") located on the sheet named Customer
    Account = Range("D2:AJ389502") located on the sheet named Account
    Miles = Range("A2:B147502") located on the sheet named Miles

    The two set of data pulled from the Pivot table go to row 180110 but there are no formula preforming lookups on those.

    If there is any other info I can provide that would be useful let me know.



    Please Login or Register  to view this content.
    Also posted this here http://www.ozgrid.com/forum/showthre...402#post723402
    Last edited by chad.portman; 08-17-2014 at 12:05 PM.

  2. #2
    Forum Contributor dlow's Avatar
    Join Date
    08-12-2013
    Location
    EveryWhere
    MS-Off Ver
    Excel 2007, 2010
    Posts
    275

    Re: Macro Run time to slow

    I would start with adding

    Please Login or Register  to view this content.
    To the begining of you code & important that you also add it to the end, but change =False to =TRUE at the end, like below.

    Please Login or Register  to view this content.
    I would agree that it is more than likely your formulas or a least the way you are telling Excel to do them. I had some formulas that were painfully slow & I scrapped them & found a different way to do it which was much faster. Im no ace, but that might be the best way to do it. I think I used some arrays formals instead.
    Last edited by dlow; 08-16-2014 at 07:55 PM.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Macro Run time to slow

    if your formulas makes it slow..
    you can switch to manual calculation..

    and after macro has been finished ..
    adding the following line will do the job...

    Please Login or Register  to view this content.


    Don't forget to click *

  4. #4
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Dlow I am trying your idea and it has been about an 1.5hours so far still running.

    Vickas thanks for the response but I guess I should be more clear I need the whole process to be faster not just the macro. Doing what you suggest as far as I can tell only changes when the calculations are done not the speed. Please let me know if I am wrong.

    I was hoping there was some vba code that would replace the formulas and run faster maybe some kinda loop or something I am not familiar with.

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Macro Run time to slow

    I have adjusted your code a bit..
    try it..

    Please Login or Register  to view this content.
    Don't forget to click *

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Macro Run time to slow

    I have ignored unusable selections in earlier part of your code..

    Vikas Gautam

  7. #7
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Thanks for that help I forgot I could do that. This cleans up the code a bit but still the speed issue is not present until the formulas are calculations. So that is the part of the code I am looking for advice on.

  8. #8
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Macro Run time to slow

    One more thing..
    Replace this
    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.
    For calculations I think... there is not any substitute available.. as they take time to calculate..
    Can just switch to manual, and afterwards press F9 to recalculate the sheet..

    Don't forget to click *

  9. #9
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Thanks for the code clean up tips i missed when i wrote it. But you are missing the point that I want to find a way other than formulas to try and get past the calculation time. I have said this several times and everytime your response has just been to change when i do the calculation.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    See if this is faster.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Out of Memory error on the line that reads:

    a = Range("Customerdata").value

    Also thanks for the effort here there is a lot in that code for me to look up and learn as i dont understand hardly any of it.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    How many rows/columns are there in Customerdata?

    If more than 33 columns then just try to change that line to
    Please Login or Register  to view this content.
    Otherwise it needs to loop through the cells....

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Or try this
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Customer data is 51 Columns wide by 558546 Rows tall.

    Testing the new code now.

  15. #15
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Got a Run-time error '1004': Application-defined or object-defined error on the line that says:

    .Value=A

    Right before the end with at the bottom of the code.

  16. #16
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    The code seemed to work fine for the first 150k records before giving that error. Also I just noticed that this is only pulling info from the customer data section when some of the columns it needs to pull are from the "Account" and "Miles" section. So those columns would not exist in the customer data section could this be the reason for the error?

    It is late here so I am going to go to bed but will be back on here tomorrow. Thanks for the help I feel this is going in the right direction.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    deleted................
    Last edited by jindon; 08-17-2014 at 03:16 AM.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  19. #19
    Forum Contributor dlow's Avatar
    Join Date
    08-12-2013
    Location
    EveryWhere
    MS-Off Ver
    Excel 2007, 2010
    Posts
    275

    Re: Macro Run time to slow

    Hi again about this post #1 you really should provide a sample workbook. Thats why this isnt solved yet. Some of the really brilliant ppl on the forum dont even waist there time unless you follow the rules, & that is kinda a rule here. I think, I havent read them in a while. Either way I do know, that if I am serious about getting my solution I better create one.
    jindon & Vikas_Gautam have given some really great solutions
    Like I said in the first reply I think arrays crunch BIG data fast, but I could be wrong, so... But I would be looking at the formulas that you using. It wouldnt hurt to see if they guys in the Formula Forum might know of a more efficient algorithm for what youre doing. Until you provide sample data its just stabs in the dark.
    VBA is not always the fastest way to do things that’s why I keep mentioning formulas, but I don’t know what Im talking about either, thats why I come here & ask questions but only when I am absolutely stuck.
    Lastly I don’t do this:
    Please Login or Register  to view this content.
    I do it with cells like this:
    Please Login or Register  to view this content.
    I do believe its more efficient [Find being faster than count], again I could be totally wrong on that.

  20. #20
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Normally I do provide a sample workbook but this contains tons of private info I can not provide to anyone. As well as the file is 440MB in size.

    Although thank you for the alternative way of finding the last row that is useful.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, message received and thanks for the link in your post #1.

    Try
    Please Login or Register  to view this content.
    Vikas_Gautam and dlow don't seem to undeerstand the issue, no need to upload the file anyway.

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Duplicate.........

  23. #23
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Got a Run-time error '1004': Application-defined or object-defined error on the line that says:

    If dic.exists(r.Value) Then r(, 2).Resize(, UBound(x)).Value = dic(r.Value)

    The underlined portion being the part that was highlighted.

    The code ran to the exact same point the previous code ran to before it gave the same error.

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Hummm, I got no error...
    Try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Same error again the underlined part below was the highlighted part during debug:

    For Each r In Cells(1).CurrentRegion.Columns(1).Cells
    If dic.exists(r.Value) Then
    For i = 0 To UBound(x)
    r.Offset(, i + 1).Value = dic(r.Value)(i)
    Next
    End If
    Next

    Yet again it is making it way through the list and stops at the same point it fills in up to Row 150066 filling in only the first two columns on that row before stopping all three times.

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Ahhhh,

    I missed, other 2 named ranges....
    Customerdata = Range("A1:AD558546") located on the sheet named Customer
    Account = Range("D2:AJ389502") located on the sheet named Account
    Miles = Range("A2:B147502") located on the sheet named Miles
    Anyway, do you have any Error value in Customerdata?
    This is only the one I can think of giving you the error.

  27. #27
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Nice call. The cell it errors on when looking up turns out to be a negaitive date entered for a B-day, a data entry error on the imported data. However as you mentioned there are 2 other named ranges which are used for just a couple of the columns. Also in the customerdata Col A has a list of numbers that are very similar to Col AD which is where the Customer ID is actually located it appears the macro you wrote is looking for the Customer ID in Col A instead of AD. The 3,16,21,and 22 from the array are the four columns of info from the customerdata table that I need and they are all located to the left of the Col which has the Cusomter ID which is why I had to use the index match instead of Vlookup for those 4. All the other numbers in the array can be found using a standard vlookup as the needed data is to the right of the lookup value. Although the account table may cause another issue as the lookup value is not Col A either but at least all the needed values are to the right of it. I hope that this makes sense. Let me know if I can provide you with more info that would be useful.

    Would a blank cell cause the same type of issue as the neg date

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, now I see the whole picture...

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    See if this produce the result except col "Q".

    You need to fix the errors in the named range.
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Out of Memory Error on the below line.

    x(i) = Range(e(0)).Columns(e(2)(i)).Value

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK.
    Try this one
    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    OK I closed out and reopened and tried that same code again. It ran but did not work at all as intended. It replace the headers and inputed the same info for every row with the first 3 Col being blank. No idea what happened

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Yeah,,,
    try
    Please Login or Register  to view this content.

  34. #34
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    I have to run for class be back in a few hours will test then

  35. #35
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    This code works so close to perfect it is crazy. It does everything in just under 2 mins the only down side is it is deleting the column headers for any data from the account or miles tables while keeping the headers from the customer data table. I could just macro in the headers again but I figure you know a quick way to correct that one thing.

  36. #36
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Header...
    Just change 1 to 2 for the loop counter in the last loop.
    Please Login or Register  to view this content.

  37. #37
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    That fixed it perfect thank you sir. Now I just need to learn what all this code actually means so I dont have to ask for your help next time

  38. #38
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  39. #39
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Well spoke to soon. Would you mind looking at the original code I posted again for the Col P and Col Q? it looks as though the info that should be in Col P is in Col Q. Also Col Q was a bit fancier than the rest instead of just a single lookup once it found the data it did a secondary vlookup. It appears that the Col P your code is making is the data from the first lookup that should be in Col Q.

    Does that make sense?

  40. #40
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    The code produces only up to col.P.

    Because Col.Q formula is bit strange to me.

  41. #41
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    I just ran it again and the code definitely input data in both Col P and Col Q. But I just realized you might not have know that it was starting in Col B.
    Last edited by chad.portman; 08-17-2014 at 10:19 PM.

  42. #42
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, just change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  43. #43
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Ok thank you. I guess for now I will continue to use a formula for Col Q then. The idea of that formula was to lookup the miles the customer lived from away from the location and then with that do a lookup across a table to place the customer in different distance bands such as local or regional.

  44. #44
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, I need to go out now for a while.

    I will think about the process for col.Q, after I come back.

  45. #45
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Thank you. I know I am asking a lot but I really appreciate it.

  46. #46
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    See if this works
    Please Login or Register  to view this content.

  47. #47
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    See if this works.
    Please Login or Register  to view this content.

  48. #48
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Duplicate.......

  49. #49
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    So this removed the headers again for the ones from Account and from Miles. Also Col Q is completely Blank. But I did not receive any errors.

  50. #50
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Can you tell me what's msgobx says and if it appears in "IBMile".

    IMPORTANT!
    How to abort the execution of the code after msgbox.
    Hit Cntrl + Break, Finish or End whatever comes 2nd from the left.
    Please Login or Register  to view this content.

  51. #51
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    So the same code that working yesterday(posted below) on my home machine when at work gives me an out of memory error on the line that is BOLD and Underlined. Any thoughts?

    Please Login or Register  to view this content.

  52. #52
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Closing out of everything and then retesting again as that fixed the out of memory issue last time. Also testing your new code will tell you about the msgbox

  53. #53
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Since you are asking if the Msgbox is in IBMile maybe I should mention that the vlookup across IBmile is not looking for an exact match but an approximate match. Not sure if that affects your code or not but just throwing it out there

  54. #54
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    The Message box said "105.18626220179" which is the first entry of Col B in the range Miles. IbMile is as follows:

    Miles Region
    -1 Local
    31 Drive

    The idea is to see how many miles they are and which region they fall into based on that by using that range. While that could be hardcoded in since it is jus two values those values change from time to time and vary from Site to Site and I am building this for several different locations.

  55. #55
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Even after closing out of everything and restarting I ran out of memory with the code that was working. Why does excel hate me?

  56. #56
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    That's right.

    If is is not exact match, it will be time consuming process via vba, so better use formula.

  57. #57
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    ok so I will use the formula for that Col but even without that Col the code that worked last night it giving me the Memory error now. I have no idea what to do about it. But I will go ahead and mark this as solved because I don't think we are getting any better than the brilliant code you made sir.

  58. #58
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    For the memory issue.
    Please Login or Register  to view this content.

  59. #59
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    I tried that but it still gave memory error

  60. #60
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    If still, possible cause is the last part.
    Please Login or Register  to view this content.

  61. #61
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    I think I figured out the issue I will test when I get to work tomorrow. I was using Excel 07 which has a memory limit of 2GB. I have had IT at work instead Excel 2013 64 bit on my PC there which will support up to 8TB of Memory thus allowing me to use the rest of the 8GB I have there. Until I can get a copy of that version of Excel for the house I can not check that here anymore.

  62. #62
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    I was right. IT was installed the 64 bit Office 2010 and now the code works. While watching the Task Manager for Memory used it uses 2.7GB of Memory to run the code which is why the 32 bit Office did not work.

  63. #63
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Then do you want to try this one?
    Please Login or Register  to view this content.

  64. #64
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    And this is with col.Q, need to be checked.
    Please Login or Register  to view this content.

  65. #65
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Will test these today. Thank you for all the help so much.

  66. #66
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Both of those last two codes say subscript of out range on the line

    a(i, ii) = dic(a(i, 1))(ii - 1)

    On a side note would it be easier to get Col Q if I added another Col and we first brought in the Miles with just one lookup into the new col and then used that Col as the lookup value and did the lookup from there across the IBMile. Instead of trying to do them both in one Col?

  67. #67
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Can you change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and try again?

  68. #68
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    When I add the Resize(,17) the macro runs just fine for everything but Col Q is left blank. I noticed you no longer had the array listed that looked at miles so I tested adding that in at the end of the arrays and was able to run it and it put the miles into Col Q. But it is still not doing the part of looking through IBmile to convert the mile to a region. As I mentioned in my last post would this be easier to do if we just used to different Col because we can do that without an issue on my end.

  69. #69
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Also do we need to clear the Dictionary at the end of this because it does not seem to be getting rid of that data as the Ram used stays super high even after the macro is done working.

  70. #70
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Ahhh, I don't know what happened...

    change
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    And add one line at the bottom before End Sub
    Please Login or Register  to view this content.

  71. #71
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Thank you for the dic=Nothing. Adding the Array("miles",1,array(2) puts the actual miles into Col Q which from the orginal formula would be the first lookup. The idea is to then take the miles and do the relative lookup across IBMIle which I believe you said would be easier to do outside of VBA due to the relative nature.

    For each customer the idea is to take the miles and determine if they are Local, Drive, Outer:

    Local is every customer with miles <=15
    Drive is every customer with miles >15 and <=90
    Outer is every customer with miles >90

    Knowing that would it be easier to code it directly in VBA instead of the lookup?

  72. #72
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    How about
    Please Login or Register  to view this content.

  73. #73
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Sorry work has been to hectic today to mess with this will have to try tomorrow.

  74. #74
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Same thing just inputs the actual miles not the region

  75. #75
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Last edited by jindon; 08-22-2014 at 04:01 PM.

  76. #76
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    So very close it puts the region in there now but of three possible regions it is only producing the higher two regions none of the low end. I did verify that there are Customers that qualify in all three regions.

  77. #77
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    Col.Q calculation is
    Please Login or Register  to view this content.
    The code puts col.Q that is Lookup value from miles first, then look up from IBMile.
    Since second lookup formula is not exact match, IBMile must be sorted ascending.

    If the set up correctly, it should produce correct value.

    Otherwise, need to see small dummy sample workbook.

  78. #78
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    IBMile is sorted ascending. I guess I can try to make a sample file when I get home with like 10-20 entries instead of the full craziness. Have enough in there to sample all the different outcomes. Will try to have that up within the next few hours.

  79. #79
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, I'll take a look when the file is uploaded.

    BTW, is there significant difference in process time?

  80. #80
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    From where I started to what we have now. We are down from about 10 hours to 6 mins but that includes all my code and not just the part you helped on. Because I just asked for help on the really hard part that was slowing me down instead of the whole thing. The whole code is about 8 pages long and that seems excessive to ask for help on.

    if you are asking if the process time is shorter from your first code to the code that works with Col Q no it is about the same I think but I can test that later tonight as well.

  81. #81
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, you can run this and get the processing time for my code when you have time.
    Please Login or Register  to view this content.

  82. #82
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Do you have a site you would perfer me to post the sample to as I dont think I can post it here due to size limits?

  83. #83
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    Timer for your code is 135.320 sec. Just over 2 mins for that part compared to 10+ hours.

  84. #84
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Macro Run time to slow

    OK, PM sent,,,

  85. #85
    Registered User
    Join Date
    07-16-2014
    Location
    OKlahoma
    MS-Off Ver
    2013 64 bit
    Posts
    90

    Re: Macro Run time to slow

    I see thanks having issues with the sample file not wanting to work at all fixing that before i send.

+ 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. Macro is running real slow and makes navigating the worksheet really slow after execution.
    By MichWolverines in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2013, 04:29 PM
  2. [SOLVED] slow computer (slow clipboard) breaks my macro
    By twilsonco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2013, 09:16 PM
  3. First time using access, should it be this slow?
    By bauerbach in forum Access Tables & Databases
    Replies: 7
    Last Post: 06-11-2012, 11:13 PM
  4. Why is my macro slow only some of the time?
    By jagman1990 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2010, 11:46 AM
  5. Macro processing time is very slow
    By Foxcan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2009, 11:44 PM

Tags for this Thread

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