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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,517
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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,517
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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

ADVERTISEMENT

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,517
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. 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

ADVERTISEMENT

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,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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,108,951
Messages
5,525,842
Members
409,666
Latest member
aquabit

This Week's Hot Topics

Top