Is there a limit to Nested If-Else-Then statements in a For Each Loop?

[AlexB123]

Hi everyone,

I'm attempting to use VBA to change the filters on two pivot tables according to current month and quarter according to a broadcast calendar. The logic for the month filter was very simple, and my code works. However, our quarters are represented on this other pivot table by selecting three months at a time. Therefore, I'm trying to loop through all the pivot fields, and use if then else statements to update ... but I am getting a Compile Error: "Next without For". What is going on here? Can anyone recommend a solution to get this to work?

Thanks,

                        Dim PT1 As PivotTable                        
                        Dim PT2 As PivotTable
                        Dim pf1 As PivotField
                        Dim pf2 As PivotField
                        Dim pvt As PivotItem
                       
                        Set PT1 = destWS4.PivotTables("PivotTable1")
                            Set pf1 = PT1.PivotFields("Month Name")
                        Set PT2 = destWS4.PivotTables("PivotTable3")
                            Set pf2 = PT2.PivotFields("Month Name")

                        With pf1

                             For Each pvt In pf1.PivotItems
                                If pvt.Name <> vStr Then
                                       pvt.Visible = False
                                Else
                                       pvt.Visible = True
                                End If
                            Next pvt

                        End With
                        
                            'Now update name of cell beside monthname
                                destWS4.Range("O19").Value = vStr

                        With pf2

                         For Each pvt In pf2.PivotItems
                                   If pvt.Name = "January" Or pvt.Name = "February" Or pvt.Name = "March" Then
                                    If vst2 = "Q1" Then
                                        pvt.Visible = True
                                    Else
                                        pvt.Visible = False
                                    End If
                                ElseIf pvt.Name = "April" Or pvt.Name = "May" Or pvt.Name = "June" Then
                                    If vst2 = "Q2" Then
                                        pvt.Visible = True
                                    Else
                                        pvt.Visible = False
                                    End If
                                ElseIf pvt.Name = "July" Or pvt.Name = "August" Or pvt.Name = "September" Then
                                    If vst2 = "Q3" Then
                                        pvt.Visible = True
                                    Else
                                        pvt.Visible = False
                                    End If
                                ElseIf pvt.Name = "October" Or pvt.Name = "November" Or pvt.Name = "December" Then
                                    If vst2 = "Q4" Then
                                        pvt.Visible = True
                                    Else
                                        pvt.Visible = False
                                    End If
                            Next pvt

 

[Joe4]

You are missing an "END IF".

Note that you have two IFs in a row here:

                                   If pvt.Name = "January" Or pvt.Name = "February" Or pvt.Name = "March" Then
                                    If vst2 = "Q1" Then

and haven't close out the first one by the time you get to the bottom of your code.

 

[AlexB123]

You are missing an "END IF".

Note that you have two IFs in a row here:

                                   If pvt.Name = "January" Or pvt.Name = "February" Or pvt.Name = "March" Then
                                    If vst2 = "Q1" Then

and haven't close out the first one by the time you get to the bottom of your code.

It's almost always something simple, lol.

Thanks!

 

[Joe4]

Yeah, that "Next without for" message can be a bit of a red herring at times.
Most of the time, if you see that error, it means you have some sort of "code block" missing its ending/closing piece, i.e.
- an "If" missing its matching "End If"
- a "Next" missing its matching "For"
- a "With" missing its matches "End With"
etc.