Stepping Through Code With Unexpected Exit From Procedure

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,631
Office Version
365, 2016
Platform
Windows
I'm discovering an odd problem ...

As I am stepping through my code, when I reach a particular line in a procedure, it suddenly jumps out of the procedure and goes back to the 'next' of the loop from which that procedure was called.

I am inclined to think that if there is an error with the line, it leaves the procedure.

I am relatively new to Excel VBA, so I can't begin to imagine why this behaviour exists. Could it have something to do with "on error resume next"? code? I don't use it very often (only a small handful of instances in my project) I know I don't have it in the procedure that is being exited, so unsure how, if at all, this may be part f it.

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,145
Could it have something to do with "on error resume next"?
I would be inclined to say yes. The "on error resume next" is hiding the error.

REM out that line so you can see the real error.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,631
Office Version
365, 2016
Platform
Windows
Thanks Jeff ...

Rich (BB code):
                'On Error Resume Next 'bypasses error created by accessing header row for 1st comparison
                pp_start = .Range("N" & i - 1)
This was the only instance of ON ERROR in the module. With it removed, I am receiving a 'Type Mismatch' error, but I'm not being directed to where. (shouldn't the erroneous line be highlighted?)

I will take a stab at saying its the line in blue, as without the preceeding 'on error' line, it is encountering an error. pp_start is declared as double, but with this line if it access n-1, which could be the header row of the data, it will encounter text and throw an error. The On Error line was put in to bypass this in this scenario.

So, what would an option be to overcome this?
 
Last edited:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,631
Office Version
365, 2016
Platform
Windows
OK, if this is OK ... I think I resolved the problem.
Added the line in green ..

Rich (BB code):
    On Error Resume Next 'bypasses error created by accessing header row for 1st comparison
        pp_start = .Range("N" & i - 1)
    On error goto 0
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,631
Office Version
365, 2016
Platform
Windows
Thanks for the good read Jeff. Not too technical so easy to digest. I've added that to my collection of good resources for the future.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,855
Messages
5,489,297
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