VBA Excel code doesn't run completely

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,362
Office Version
2019, 2016, 2013
Platform
Windows
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
 

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,362
Office Version
2019, 2016, 2013
Platform
Windows
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
 

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
Hi Mole

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

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,362
Office Version
2019, 2016, 2013
Platform
Windows
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:

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,102
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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
 

vonguyenphu

New Member
Joined
May 26, 2019
Messages
29
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
    [B]SourceSh.Cells(5, 4).CurrentRegion.AutoFilter Field:=4, Criteria1:="p*"[/B]
'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:

Watch MrExcel Video

Forum statistics

Threads
1,101,850
Messages
5,483,303
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top