Macro terminated after the first condition in IF is met

[jasonprescott]

Hello guys,

I have this code of mine in the Macro (And yea, I tried using the classis "i" as a variable, did not work so I changed it to "g")

For g = 1 To 20
    
            If (InStr(1, salesWS.Cells(g, 2).Value, "X004a", 1) > 0 And InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1) > 0 And InStr(1, salesWS.Cells(g, 4).Value, "PLN", 1) > 0) Then
            pivotWS.Cells(9, 10).Value = salesWS.Cells(g, 5).Value 'FIRST STEP DONE
            pivotWS.Cells(9, 11).Value = -Abs(salesWS.Cells(g, 7).Value) 'SECOND STEP SKIPPED
                        
            ElseIf (InStr(1, salesWS.Cells(g, 2).Value, "X004a", 1) > 0 And InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1) > 0 And InStr(1, salesWS.Cells(g, 4).Value, "EUR", 1) > 0) Then
            pivotWS.Cells(9, 12).Value = salesWS.Cells(g, 5).Value
            pivotWS.Cells(9, 13).Value = -Abs(salesWS.Cells(g, 7).Value)
            
            ElseIf (InStr(1, salesWS.Cells(g, 2).Value, "X004c", 1) > 0 And InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1) > 0 And InStr(1, salesWS.Cells(g, 4), "PLN", 1) > 0) Then
            pivotWS.Cells(11, 10).Value = salesWS.Cells(g, 5).Value
            pivotWS.Cells(11, 11).Value = -Abs(salesWS.Cells(g, 7).Value)
            
            ElseIf (InStr(1, salesWS.Cells(g, 2).Value, "X004c", 1) > 0 And InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1) And InStr(1, salesWS.Cells(g, 4).Value, "EUR", 1) > 0) Then
            pivotWS.Cells(11, 12).Value = salesWS.Cells(g, 5).Value
            pivotWS.Cells(11, 13).Value = -Abs(salesWS.Cells(g, 7).Value)
            
            End If
    Next

The problem is, after the first IF matches the condition, it does the "First step done" and then it goes automatically to the "End Sub" no nevertheless there still is some other code after this FOR loop.

 

[JLGWhiz]

Does the highlight actually go to 'End Sub' or does the procedure just terminate without any other line being highlighted? big difference.

 

[Anthony47]

In addtion to @JLGWhiz question, above...
Can you tell us in which vba Module is this code hosted? Please read the full title of the Window, from "Microsoft Visual Basic, Application Edition" on.

Bye

 

[jasonprescott]

Hey guys, thanks for your replies,
The full title is:

And when debugging the code, it jumps to End Sub of the current one it is contained in, then to the superior which this one is called from and then it's just the end.

 

[JLGWhiz]

Next question, do you have worksheet event code in pivotWS and/or do you have volatile formulas that use functions like Today(), Now(), Offset(), Indirect(), Index(), Areas(), Columns() or Rows()? Also, are there any merged cells on pivotWS in the ranges where data will be populated by code? These may or may not be causes of the problem, but if we can eliminate them it will get us closer to the actual cause.

Also, tell me what value the variable g contains after the code jumps to End Sub. You can get that by hovering the pointer and reading the intellisense.

 

[Anthony47]

Is AnalyticalMacros a "standard" vba module or a "class" module? Is there any worksheet named AnalyticalMacros ?
It should not be so crucial, but I would always set, within each of the Instr(Begin, String, TextToSearch, SearchMode), the fourth field (SearchMode) to 1=vbTextCompare

Also, for testing add these "Debug.Print" lines into your code:

For g = 1 To 20
    Debug.Print g
    Debug.Print "A: " & InStr(1, salesWS.Cells(g, 2).Value, "X004a"), salesWS.Cells(g, 2).Value
    Debug.Print "AA: " & InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1), salesWS.Cells(g, 3).Value
    Debug.Print "AAA: " & InStr(1, salesWS.Cells(g, 4).Value, "PLN", 1), salesWS.Cells(g, 4).Value
'this is you first IF block, with SearchMode=1:
    If InStr(1, salesWS.Cells(g, 2).Value, "X004a", 1) > 0 And InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1) > 0 And InStr(1, salesWS.Cells(g, 4).Value, "PLN", 1) > 0 Then
        Debug.Print "Ifs=True; 1"
        pivotWS.Cells(9, 10).Value = salesWS.Cells(g, 5).Value
        Debug.Print "Ifs=True; 2"
        pivotWS.Cells(9, 11).Value = -Abs(salesWS.Cells(g, 7).Value)
        Debug.Print "Ifs=True; 3"
    End If
    'Your additional instructions (additional IF blocks)

After this adding, try intercepting the error (add a Stop or a breakpoint on the code that follows this macro); after the error, open the "vba Immediate window" (typing Contr-g should be enough; or Menu /View /Immediate window) and report which are the last 10 lines that are listed (or All, if less than 10)

Bye

ps: Sorry @JLGWhiz, I took long time in rewriting my message, and saw your message only at the end
I hope that the user will not get confused by my message

 

[jasonprescott]

Is AnalyticalMacros a "standard" vba module or a "class" module? Is there any worksheet named AnalyticalMacros ?
It should not be so crucial, but I would always set, within each of the Instr(Begin, String, TextToSearch, SearchMode), the fourth field (SearchMode) to 1=vbTextCompare

Also, for testing add these "Debug.Print" lines into your code:

For g = 1 To 20
    Debug.Print g
    Debug.Print "A: " & InStr(1, salesWS.Cells(g, 2).Value, "X004a"), salesWS.Cells(g, 2).Value
    Debug.Print "AA: " & InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1), salesWS.Cells(g, 3).Value
    Debug.Print "AAA: " & InStr(1, salesWS.Cells(g, 4).Value, "PLN", 1), salesWS.Cells(g, 4).Value
'this is you first IF block, with SearchMode=1:
    If InStr(1, salesWS.Cells(g, 2).Value, "X004a", 1) > 0 And InStr(1, salesWS.Cells(g, 3).Value, "TOBI", 1) > 0 And InStr(1, salesWS.Cells(g, 4).Value, "PLN", 1) > 0 Then
        Debug.Print "Ifs=True; 1"
        pivotWS.Cells(9, 10).Value = salesWS.Cells(g, 5).Value
        Debug.Print "Ifs=True; 2"
        pivotWS.Cells(9, 11).Value = -Abs(salesWS.Cells(g, 7).Value)
        Debug.Print "Ifs=True; 3"
    End If
    'Your additional instructions (additional IF blocks)

After this adding, try intercepting the error (add a Stop or a breakpoint on the code that follows this macro); after the error, open the "vba Immediate window" (typing Contr-g should be enough; or Menu /View /Immediate window) and report which are the last 10 lines that are listed (or All, if less than 10)

Bye

ps: Sorry @JLGWhiz, I took long time in rewriting my message, and saw your message only at the end
I hope that the user will not get confused by my message

Hello, sorry, I am quite ill nowadays. I ran it and debugged the code which is faulting.

 1 
A: 0          SalesIncome
AA: 0         Total
AAA: 1        PLN
 2 
A: 0          SalesIncome
AA: 0         Total
AAA: 0        EUR
 3 
A: 0          SalesIncome
AA: 1         TOBI
AAA: 1        PLN
 4 
A: 0          SalesIncome
AA: 1         TOBI
AAA: 0        EUR
 5 
A: 0          SalesIncome
AA: 2         xTOBI
AAA: 1        PLN
 6 
A: 0          SalesIncome
AA: 2         xTOBI
AAA: 0        EUR
 7 
A: 0          X001
AA: 2         xTOBI
AAA: 1        PLN
 8 
A: 0          X002
AA: 2         xTOBI
AAA: 1        PLN
 9 
A: 0          X003
AA: 2         xTOBI
AAA: 1        PLN
 10 
A: 1          X004a
AA: 1         TOBI
AAA: 1        PLN
Ifs=True; 1
Ifs=True; 2

It still does only the first line after it evaluates the IF as true, skips the 2nd line and automatically shuts down the current procedure. I also added the last parameter of the InStr() method.

 

[Anthony47]

Hummm
Don't you see also an Ifs=True; 3 in the Immediate window?

Has worksheet pivotWS any event macro associated with it?

Is AnalyticalMacros a "standard" vba module or a "class" module? Is there any worksheet named AnalyticalMacros ?

Your answer?

Bye