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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
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,368
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,368
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,177
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,102,860
Messages
5,489,311
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top