Consulting

Results 1 to 4 of 4

Thread: Else statement in loop

  1. #1

    Else statement in loop

    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

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by 9!GR@BzyQ37b View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •