VBA Excel code doesn't run completely
Results 1 to 10 of 10

Thread: VBA Excel code doesn't run completely

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Excel code doesn't run completely

    I have a macro but when i run, i only run to specific line code (of course i don't use break point mode or F9)
    Eg:
    Line 1: .....
    "
    code here
    "
    Line 20: ...
    "
    code here
    "
    Line End:...

    It only runs to Line 20 and stop there, i have to press F5 if i want to continue. That is like break point mode but i actually don't use F9. Pls help me for this case

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,818
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    i see the problem

    what you have shown is not VBA

    give us a chance to consider what you are doing by posting the macro between code tags and you might get an answer
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    Hi Mole
    FYI. The problem is it run perfectly yesterday, but i've only got the problem today. I think it might not be problem with code because i met this before and on a good day it disappeared
    Dim coBook As Workbook
    Dim lRow As Long
    On Error Resume Next
    Workbooks("Y19.xlsx").Activate
    If Workbooks("Y19.xlsx") Is Nothing Then
    Workbooks.Open fileName:="S:\Year 2019\REPORT\Y19.xlsx", ReadOnly:=True
    End If

    Sheets("Vessel").Activate
    Range(Cells(Rows.Count, 5).End(xlUp), Cells(4, 5)).SpecialCells(xlCellTypeVisible).Copy

    If Workbooks("China Co.xlsx") Is Nothing Then
    Workbooks.Open fileName:="D:\Chart Co China\China Co.xlsx"
    End If
    Workbooks("China Co.xlsx").Activate
    On Error GoTo 0
    Set coBook = Workbooks("China Co.xlsx")
    Range("j2").PasteSpecial Paste:=xlPasteValues
    Range(Range("j2"), Range("J2").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    ''' code stop here
    lRow = Cells(Rows.Count, 10).End(xlUp).Row
    Rows(lRow + 1 & ":" & 191).Delete shift:=xlUp
    coBook.SaveAs fileName:="D:\Chart Co China\China Co ETD " & Month(Range("b2")) & "-" & day(Range("b2")) & ".xlsx"
    Range("A2", "M" & lRow).Sort key1:=Range("j1:j" & lRow), order1:=xlAscending

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,818
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    presuming stepping through with F8 and China Co loads cleanly, stop the code, then look at the workbook, in J are there duplicates. I suspect none are found
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    Hi Mole

    It works now, but i don't understand the problem. Besides how can i post my code in a better way?

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,818
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    code is posted between Code Tags

    without spaces [ CODE ] your code here [ / CODE ]

    I suspected that there were no duplicates so the selection fails to provide any rows to work with
    Last edited by mole999; Jun 27th, 2019 at 10:49 AM.
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    Hi Mole,

    The problem occurs again, this time with others Macro too. Because yesterday i didn't do any change to these Macro, but today they stop in the middle of execution without any warning even when i stepped via F8. They worked perfect for a long time.
    I believe there are some problems with My Excel (version 2010) or system rather than the code. Do you have any idea?

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,661
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    This is an occasional problem. It usually occurs if you've been debugging code and have stopped it mid execution. Restarting Excel and/or your PC generally cures it.

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    That's correct Rory. I did met this problem before and simply had it fixed by restarting my Excel or PC. But this time, it didn't work but maybe tomorrow it'll return normal. It's hard to cure something if you don't actually understand it

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Excel code doesn't run completely

    Hi Rory,

    Finally i narrowed down the problem when running via F8
    Code:
        Dim SourceWb As Workbook    
        Dim SourceSh As Worksheet
        Set SourceWb = ActiveWorkbook
        Set SourceSh = ActiveSheet
        Dim BuyerCode As String
        SourceSh.Cells(5, 4).CurrentRegion.AutoFilter Field:=4, Criteria1:="p*"
    'This sub stop here (the below line are still excuted but following codes are not running without any warning Error)
        BuyerCode = LCase(InputBox("Enter the name of buyer", "Buyer Code Input"))
    .......
    Last edited by vonguyenphu; Jul 19th, 2019 at 11:30 PM. Reason: Code editing

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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