VBA Excel code doesn't run completely

[vonguyenphu]

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

 

[mole999]

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]

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]

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]

Hi Mole

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

 

[mole999]

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

 

[vonguyenphu]

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]

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]

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]

Hi Rory,

Finally i narrowed down the problem when running via F8

    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"))
.......