+ Reply to Thread
Results 1 to 27 of 27

Excel 2013 keep crashing when i run macro

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Unhappy Excel 2013 keep crashing when i run macro

    My excel 2013 keep crashing whenever i run this macro .
    Is there something wrong with my code that crashes my excel .
    Please help ,thank you

    Please Login or Register  to view this content.
    Last edited by fluffyvampirekitten; 07-06-2015 at 06:00 AM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Excel 2013 keep crashing when i run macro

    Hi, your mistake is with the Colums "P " select and the Range("P3 " & MaxRowNum)
    The macro could be simplified and I think this will do the job
    Please Login or Register  to view this content.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    It didn't work. It keep processing and it slow downs my laptop

  4. #4
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    I have added a few codes. I'm still facing the same problem .
    Please Login or Register  to view this content.
    Last edited by fluffyvampirekitten; 07-06-2015 at 06:00 AM.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Excel 2013 keep crashing when i run macro

    Of course it slows down your laptop or anything you're using, yo address the file MISSINGUSERNAMEDEPT in every loop.
    Yoiu'll have to think up another strategy

  6. #6
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Do you think I can use "For Each Loop"?

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    you are inserting vlookup for the row with not indicated but then your filling down the formula anyway? and then your looping it for each row?

    either remove the autofill or just autofill without loop?
    also since your using a do while loop...is your rownum even incrementing?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    also this
    Please Login or Register  to view this content.
    seems like an awfully complicated way to find maxrow
    unless your data structure absolute calls for it...
    is there a specific reason why did not you take up the suggestion provided by Keebellah

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    I tried out the code given by Keebellah but it keep processing and wont stop.

  10. #10
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    My idea is to find the row which displays "Not indicated" under UserName & Dept Column in SIMPAT file.
    And then open up another workbook called MISSINGUSERNAMEDEPT to find the UserName & Dept using the VLOOKUP function.
    Hence , If data(UserName/Dept) is found from the MISSINGUSERNAMEDEPT , this data will be pasted over in the SIMPAT file else "NOT INDICATED" .

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    your vlookup is the error
    this formula
    Please Login or Register  to view this content.
    generates this
    Please Login or Register  to view this content.
    you cannot vlookup a column onto another column
    also your missing sheet name

    your formula should look something more like
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by fluffyvampirekitten View Post
    My idea is to find the row which displays "Not indicated" under UserName & Dept Column in SIMPAT file.
    And then open up another workbook called MISSINGUSERNAMEDEPT to find the UserName & Dept using the VLOOKUP function.
    Hence , If data(UserName/Dept) is found from the MISSINGUSERNAMEDEPT , this data will be pasted over in the SIMPAT file else "NOT INDICATED" .
    if your idea is to only update rows with not indicated then you shouldnt autofill at all
    autofill will over write every row from 3 to maxrow and you are doing it every single time

    your logic for the formula needs to be updated to only take in the rownum
    however since your using do/while loop and not incrementing rownum...condition of
    Please Login or Register  to view this content.
    will never be met

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    This is example of what it could look like
    i dont know what your sheet name is called for your vlookup
    edit sheet1 to whatever your sheet name is on the MISSINGUSERNAMEDEPT file

    i also assumed C1:C5 for your vlookup as i dont know which column your starting from or ending
    change this to suit your needs
    C1:C5 = A:E


    Please Login or Register  to view this content.
    i assume your maxrownum is working correctly....

  14. #14
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Excel 2013 keep crashing when i run macro

    Also, note that:
    Destination:=Range("P3:Q " & MaxRowNum) has a space after the Q, that's not allowed "P3:Q n" is not a valid address.
    Ben Van Johnson

  15. #15
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by humdingaling View Post
    your vlookup is the error
    this formula
    Please Login or Register  to view this content.
    generates this
    Please Login or Register  to view this content.
    you cannot vlookup a column onto another column
    also your missing sheet name

    your formula should look something more like
    Please Login or Register  to view this content.


    My ask why is it RC[-3]?
    Sorry , I'm still new with excel vba macro .

  16. #16
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by protonLeah View Post
    Also, note that:
    Destination:=Range("P3:Q " & MaxRowNum) has a space after the Q, that's not allowed "P3:Q n" is not a valid address.
    Thank you so much. I didnt know it is space sensitive. I will take note of it .

  17. #17
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by humdingaling View Post
    This is example of what it could look like
    i dont know what your sheet name is called for your vlookup
    edit sheet1 to whatever your sheet name is on the MISSINGUSERNAMEDEPT file

    i also assumed C1:C5 for your vlookup as i dont know which column your starting from or ending
    change this to suit your needs
    C1:C5 = A:E


    Please Login or Register  to view this content.
    i assume your maxrownum is working correctly....
    Yeah , the maxrownum is working correctly .
    Thank you so much It can work.
    Could i ask one more thing , Is it possible to replace "#N/A" to "NOT INDICATED" ?

  18. #18
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    you are using FormulaR1C1 method
    i assume because you just recorded marco and used whatever it gave you and tried to amend it
    thats ok....its how i learnt also

    R = Row
    C = Column
    R(0)C(-3) = 0 rows, 3 columns back..relative to your range

    take this line of code
    and assuming rownum is 30

    Please Login or Register  to view this content.
    range would (P30)
    RC[-3] = p30 + zero rows + (- 3 columns) = M30
    explained from above C1:C5 = A:E

    the formula now reads
    vlookup(m30,[Workbook]Worksheet!A:E,2,0)

    more reading about it can be found here
    https://excelmate.wordpress.com/2013...e-style-vs-a1/

    alternatively don't use R1C1 just use .formula instead

    Please Login or Register  to view this content.

  19. #19
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    Could i ask one more thing , Is it possible to replace "#N/A" to "NOT INDICATED" ?
    you want this in built into your macro or are you going to just use it once?
    if only once then i suggest you just highlight the column
    Ctrl+H
    Find "#n/a"
    Replace with "NOT INDICATED"

  20. #20
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    if you want it part of the code
    Please Login or Register  to view this content.
    add two more IF blocks

  21. #21
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by humdingaling View Post
    if you want it part of the code
    Please Login or Register  to view this content.
    add two more IF blocks
    Normally , i do this manually
    Ctrl+H
    Find "#n/a"
    Replace with "NOT INDICATED"
    and I want to automate the process cos I'm adding more methods after this Method .

    Anyway, Thank you so much . I really really appreciate your help and your quick response. Thank you
    Last edited by fluffyvampirekitten; 07-07-2015 at 02:50 AM.

  22. #22
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Excel 2013 keep crashing when i run macro

    forgot #N/A has special rules -
    reading here if your interested


    change code to

    Please Login or Register  to view this content.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Excel 2013 keep crashing when i run macro

    RC[-3] means the cell in the current row with an offset of -3 columns wo if your active cell is D3 then RC[-3] points to A3
    And yes, evrytime you open the MISSING.... file it takes time and you think you have all the rows to look at 1048576, well that's a lot.
    The code as it is is not good, but without really understanding what you want to do and why the fill of the entire P column, sorry it's all unclear.

  24. #24
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Well , Other cells can display "NOT INDICATED" but if both Cells(rownum, 16) and Cells(rownum, 17) are #N/A , it still shows #N/A and then the error message will pop up.

    Thanks I will read up all the resources that you have given me . I really need it .
    Last edited by fluffyvampirekitten; 07-07-2015 at 03:11 AM.

  25. #25
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by Keebellah View Post
    RC[-3] means the cell in the current row with an offset of -3 columns wo if your active cell is D3 then RC[-3] points to A3
    And yes, evrytime you open the MISSING.... file it takes time and you think you have all the rows to look at 1048576, well that's a lot.
    The code as it is is not good, but without really understanding what you want to do and why the fill of the entire P column, sorry it's all unclear.



    Yeah , it was really alot of records.
    Thats why i thought of using the macro to automate the steps as it quite tedious to do it manually.
    Anyway, thank you for helping me . It took me days to do this as I have never write macro before and I just started learning excel vba.

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Excel 2013 keep crashing when i run macro

    There is a function ISNA to trap for these errors.
    Of course there are mny sites to help.
    This one is ok http://www.excel-easy.com/
    This one I frequently visit to look at solutions and samples, most macros work also for 2013
    http://www.cpearson.com/Excel/Topic.aspx
    If you need help just post it
    Happy coding

  27. #27
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    66

    Re: Excel 2013 keep crashing when i run macro

    Quote Originally Posted by Keebellah View Post
    There is a function ISNA to trap for these errors.
    Of course there are mny sites to help.
    This one is ok http://www.excel-easy.com/
    This one I frequently visit to look at solutions and samples, most macros work also for 2013
    http://www.cpearson.com/Excel/Topic.aspx
    If you need help just post it
    Happy coding
    Omg. They seems like a great website . I will start studying those things .
    I have long way to go to finish up my SIMPAT macroT^T
    Thank you so much. I really appreciated it .

+ 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. Custom cell formatting crashing Excel 2013
    By rpdhuk in forum Excel General
    Replies: 1
    Last Post: 06-23-2015, 12:29 PM
  2. VBA code causing memory leak. Crashing Excel 2013/64. Please help.
    By kabammi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-10-2015, 11:19 PM
  3. Macros crashing Excel 2013
    By Thor42 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2015, 05:13 PM
  4. Excel 2013 Crashing on opening a XLSM file
    By Wongadob in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2014, 05:10 AM
  5. Replies: 3
    Last Post: 07-20-2014, 11:46 AM

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