Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Why would a IF THEN result jump up to a prior line in the VBA code
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.
I was hoping the question was obvious enough without an example to offer some suggestions to troubleshoot.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your Sub may be being called again (likely through a chain of other subs) by whatever is in the If/Then statement.
For example if you run Subc in this code, it will behave as you describe when x becomes 5.
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.
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
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.
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
***** 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
On Error GoTo NextBadLine
In general, you should either move the On Error to the line before going into the loop, and then reset it after the loop; or leave as-is and reset after the error handling label - but I cannot recommend that, setting On Error in a conditional statement is very bad style and prone to errors, as you can see here.
The On Error needs to be executed prior to the part you want to "protect". It sets up something used until you say otherwise (On Error Goto 0 to switch it off, for example).
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial