Else statement in loop

[9!GR@BzyQ37b]

Hi,

Probably I’m doing something wrong with my code. I’ve an IF and Else statement that’s working fine. But when it’s an Else the macro goes to the follow macro. That’s also working fine. But when he finished the next macro he goes back to the first macro and starts after the End IF statement. But I expect that the macro will end at the end of the second macro.

Do I something wrong in the code?

Thanks,
Peter

Sub SanneSprint30()
 
Dim NewRng As Long
    NewRng = Blad1.Range("B" & Rows.Count).End(xlUp).Row
Dim rFiltered As Range
 
 
'filter aanzetten
 
    Sheets("Data").Select
    Rows("5:5").Select
    Selection.AutoFilter
   
 
 
Set rFiltered = ActiveSheet.AutoFilter.Range
 
'geeft aan welke kolom je gaat filteren en wat de filter criteria is
 
    With Range("$A$5:$BC" & NewRng)
        .AutoFilter Field:=Sheets("Waarden").Range("B34"), Criteria1:="<>"
       
   
'als er geen filter resultaat is dan kan er niets gekopieerd worden e nloopt het programma vast
'daarom wordt hier gekeken of er minder dan 1 resultaat is
'als dit zo is gaat hij naar de volgende macro
'is dit wel zo dan gaat hij verder met het kopieerten van het resultaat
       
  If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
 
 
'hier gaat hij de kolommen kopieeren en plakken op het tabblad Overzicht
   
    rFiltered.Offset(1, 0).Resize(rFiltered.Rows.Count - 1).Columns("A:C").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Overzicht").Select
    Range("B10").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    rFiltered.Offset(1, 0).Resize(rFiltered.Rows.Count - 1).Columns("GZ").Copy
    Sheets("Overzicht").Select
    Range("E10").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
'hier gaat hij naar de volgende macro als er geen resultaten zijn
'hij zet ook eerst de filter weer uit, anders gaat de volgende macro mis
   
    Else
    Sheets("Data").Select
    Selection.AutoFilter
    SanneSprintEnd
   
   
    End If
    End With
   
'hier gaan we de tekst sprint X toevoegen
   
    Range("F10").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Do Until IsEmpty(ActiveCell.Offset(0, -4).Value)
    ActiveCell.Value = Sheets("Waarden").Range("A13")
    ActiveCell.Offset(1, 0).Select
    Loop
   
'hier gaan we de tekst sprint X toevoegen
   
    Sheets("Data").Select
    Selection.AutoFilter
  
   
End Sub
 
Sub SanneSprintEnd()
 
 
     
    Sheets("Overzicht").Select
    Range("A1").Select
   

 
End Sub

[georgiboy]

Try putting this line:

Exit Sub

under this line:

SanneSprintEnd

The macro will jump out and run the other macro, it will always come back to the original macro to carry on unless you tell it to stop.

[9!GR@BzyQ37b]

Hi Georgiboy,

Thank you for this solution. I didn't know that I must use a Exit Sub. So I'll remember that!

Thanks,
Peter

[Bob Phillips]

I didn't know that I must use a Exit Sub. So I'll remember that!

You wouldn't normally, in your case you want to stop that procedure early, so exiting is one way.

IMO, you should be moving the code after the Else … End If into the IF part of the code if that is the only time that you want it executed. Then you would not need to add the Exit Sub.