CODEWITHSUNDEEP
excelvbaif-statementslicers
JLCH
JLCH

Reputation: 803

Exit a slicer selection loop

didn't find any answer on the web, so there we go:

I'm trying to find a way to exit an if statement before the i count for slicers is completed.

Let's say I have 2 slicers: a) country and b) regions. When a country is selected, only a few regions are possible to select which I then export one-by-one.

[...]
With ActiveWorkbook.SlicerCaches("Slicer_Region")
     '[.... something with i=1 which is different]
     For i = 2 To .SlicerItems.Count
            If Cells(41, 3).Value <> "" Then
                .SlicerItems(i).Selected = True
                .SlicerItems(i - 1).Selected = False
                Name = .SlicerItems(i).Name
                strFName = "Region " & Name & ".pdf"
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                strPath & strFName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            'Else:
            '    End If
            '    GoTo goout
            End If
        Next i
'goout:
End With 
[...]

The condition If Cells(41, 3).Value <> ""looks if there is data in the pivot - this would be the first row with data. Thereby I avoid exporting empty regions. Yet, the loop will still go on for every region, even if its not possible (not in the country which is already selected).

As soon as there is one 'empty region' all following will be empty as well (as the slicers show the populated ones first). Therefore as soon as the condition goes to an empty Cells(41, 3).Value <> "" I'd like it to exit the loop, because it just takes up time for nothing.

I don't know how to do that, the way it is in the code up here give me an error: "endif without block if" - which makes sense

Upvotes: 0

Views: 89

Answers (1)

Tim Stack
Tim Stack

Reputation: 3248

This isn't wrong, but not desirable: 

With ActiveWorkbook.SlicerCaches("Slicer_Region")
 '[.... something with i=1 which is different]
 For i = 2 To .SlicerItems.Count
        If Cells(41, 3).Value <> "" Then
            .SlicerItems(i).Selected = True
            .SlicerItems(i - 1).Selected = False
            Name = .SlicerItems(i).Name
            strFName = "Region " & Name & ".pdf"
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            strPath & strFName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Else
            GoTo goout
        End If
    Next i
goout:
End With

Rather, use the Exit For statement. You would generally want to avoid line labels

'[..]
        Else
            Exit For
        End If
    Next i
End With

Upvotes: 2

Related Questions