[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

VBA Select Case problem with dates in Pivot table

Posted on 2010-04-04
2
Medium Priority
?
494 Views
Last Modified: 2013-12-26
I am having trouble getting a Select Case statement to:
'Select all "Wk THURSDAY" dates in Month of Report date, and hide the rest
The code doesn't work as intended.

It goes to "case else" for every date in pivot table, when it should go to one of the other "case pi.value" options when the the month and year of the date being considered are the month and year of report date. The report date ("dt" in code) is 11/02/2010 (UK) or 02/11/2010 (US).
No error message is displayed.

There is one pivot table on my worksheet.
An xls file of a copy of the pivot table (values and formats only), and the vba code of the slect case statement is attached.
Please help. Thank you.
'Select all "Wk THURSDAY" dates in Month of Report date, and hide the rest
    For Each pt In ActiveSheet.PivotTables
        For Each pf In pt.RowFields
            For Each pi In pf.PivotItems
                Select Case pi.Value
                    Case pi.Value Like Month(dt) & "/*" & "*/" & Year(dt)
                        pi.Visible = True
                    Case pi.Value Like Month(dt) & "/*" & "/" & Year(dt)
                        pi.Visible = True
                    Case Else
                        pi.Visible = False
                End Select
            Next pi
        Next pf
    Next pt

Open in new window

VBA-Case-question---Ex.-Exchange.xls
0
Comment
Question by:dev00790
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 29716889
Why not simply test on month and year directly?  Replace:

                Select Case pi.Value
                    Case pi.Value Like Month(dt) & "/*" & "*/" & Year(dt)
                        pi.Visible = True
                    Case pi.Value Like Month(dt) & "/*" & "/" & Year(dt)
                        pi.Visible = True
                    Case Else
                        pi.Visible = False
                End Select

with:

                pi.Visible = (Month(pi.Value) = Month(dt) And Year(pi.Value) = Year(dt))
0
 
LVL 1

Author Closing Comment

by:dev00790
ID: 31710790
Brilliant! Thanks a lot. :-)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question