We help IT Professionals succeed at work.

Why would a IF THEN result jump up to a prior line in the VBA code

upobDaPlaya
upobDaPlaya asked
on
12 Comments
3 Solutions
240 Views
Last Modified: 2017-04-15
Without providing an example.  I can get it later if needed.  If I have a For Next loop and within the For Next Loop I have an IF THEN statement.  For some reason when the IF Statement runs it jumps back to a prior line of code above the Loop to another IF THEN statement.  What would be a reason for this to occur. Note if I Debug-Compile no errors are found.   Realize this question may be difficult to evaluate without an example but I am not home yet...

I was hoping the question was obvious enough without an example to offer some suggestions to troubleshoot.
Comment
Watch Question

View Solutions Only

Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Your Sub may be being called again (likely through a chain of other subs) by whatever is in the If/Then statement.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
For example if you run Subc in this code, it will behave as you describe when x becomes 5.

Sub Suba()
    Call Subb
End Sub

Sub Subb()
    Call Subc
End Sub

Sub Subc()
    Dim X As Integer
    
    MsgBox "I'm here"
    For X = 1 To 10
        If X = 5 Then Call Suba
    Next
    
End Sub

Open in new window

Note that I added the "Call"s for the purposes of this example even though they aren't necessary.

When you get in front of your computer again, put a breakpoint on the If/Then line (or one of the lines) by clicking in the left-hand margin of one of those lines and then repeatedly press F8 to step through the code. For more help with debugging please see this article of mine.
upobDaPlaya

Author

Commented:
Here is the partial code

***** FIRST BLOCK OF CODE *********
Sheets("My Sales").Select
For intR = 2 to Range ("A2").End(xlDown).Row
If Range ("J" & intR).Value = "Bad" THEN
 With Sheets ("My Sales")
  If .Range("C" & intR) = '' Then
     Exit For
  Else
  Range("A" & intR).EntireRow.Delete
  On Error GoTo NextBadLine
  Int R = int R-1
  End If
End With
NextBadLine

End If    'Jump line -  When the code runs and it gets to the code below (See "Problem Line" below) it jumps back up to this line
Next intR
IntR = 0

**** SECOND BLOCK OF CODE *******
With ActiveWorkbook
Set ws = .Worksheets ("People Data")
For intR = 2 to Range ("A2").End(xlDown).Row
   Set ws1 = .Worksheets ("Sales Data")
    For intZ = 2 to Range ("a2").End(xlDown).Row
    If Range ("F" & intR) & Range ("I" & intR) & Abs(Range("K" & intR)) = .Range("a"& intZ) & .Range ("E" & intZ) & .Range("F" & intZ) Then  'PROBLEM LINE - When IT GETS HERE it jumps up to the line I call above the "Jump line"
    .Range("A" & intZ).Value = "REVIEW"
   End IF
   Next intZ
Next intR
End With
"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
What error(s) are you trying to handle with this?
On Error GoTo NextBadLine

Open in new window

upobDaPlaya

Author

Commented:
If there is some type of error when I attempt to delete I wanted to continue
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
upobDaPlaya

Author

Commented:
Ok...i will fix that error..but the reason for the issue was I did not explicitly name the ranges.  I needed to ws.range and ws1.range.  Any other suggestions please let m,e know..thx
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If one or more of us have answered this question then please don't forget to close it. Thanks.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions