Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable Field items code -problems
Hi,
I am having a problem with my pivottable code. What I want to do is show all items for which a certain field is blank. I want it to be able to update and continue filtering with a single macro. My code sets all items in the field to be invisible and then makes the (blank) items visible. I came up with the initial code by recording a macro, but the default macro action individually sets each tiem. Since the specific items will keep changing (they are dates). I need to set items to be invisible without knowing beforehand what they are. So, without further ado, my code: Private Sub CommandButton1_Click() Dim x As Integer, itemNum As Long 'creates a pointer to pivottable "unreconciled" or whatever its name will end up being Dim URPivot As PivotTable Set URPivot = ActiveSheet.PivotTables(1) 'Refresh the pivottable URPivot.PivotCache.Refresh 'reset the field item filter itemNum = URPivot.PivotFields("Date Invoiced").PivotItems.Count With URPivot.PivotFields("Date Invoiced") For x = 0 To itemNum - 1 .PivotItems(x).Visible = False #########exception!!!!########## Next x .PivotItems("(blank)").Visible = True End With End Sub So the problem is on the line where I noted the exception, it crashes and I cant figure out why. Any ideas? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable Field items code -problems
"Rayo K" wrote: Hi, I am having a problem with my pivottable code. What I want to do is show all items for which a certain field is blank. I want it to be able to update and continue filtering with a single macro. My code sets all items in the field to be invisible and then makes the (blank) items visible. I came up with the initial code by recording a macro, but the default macro action individually sets each tiem. Since the specific items will keep changing (they are dates). I need to set items to be invisible without knowing beforehand what they are. So, without further ado, my code: Private Sub CommandButton1_Click() Dim x As Integer, itemNum As Long 'creates a pointer to pivottable "unreconciled" or whatever its name will end up being Dim URPivot As PivotTable Set URPivot = ActiveSheet.PivotTables(1) 'Refresh the pivottable URPivot.PivotCache.Refresh 'reset the field item filter itemNum = URPivot.PivotFields("Date Invoiced").PivotItems.Count With URPivot.PivotFields("Date Invoiced") For x = 0 To itemNum - 1 .PivotItems(x).Visible = False #########exception!!!!########## Next x .PivotItems("(blank)").Visible = True End With End Sub So the problem is on the line where I noted the exception, it crashes and I cant figure out why. Any ideas? Thanks! Could someone HELP TRANSLATE this simple psuedo code into MACRO please?! HTML Code:
for EACH row in WORK { for each row in DATA { if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name)) Then Highlight current WORK.row to YELLOW, break out of current for loop ELSE next DATA row++ } next WORK row++ } [size="3"](see below for explaination) I have a Excel workbook called FIRSTAM8.xls Inside I have two seperate worksheets: 1)'DATA' & 2)'WORK' There are 31886 rows in sheet 'WORK' & 5741 rows in sheet 'DATA' Column 'C' in 'WORK' contains the customer name, Column 'B' contains the zip code. In the 'DATA' sheet Column 'H' contains the customer name, & Column 'S' the zip code. I want to compare (individually, one-by-one) the zip && name Columns ('B' & 'C') of each row in sheet 'WORK' to/with/against the respective zip && name Columns ('S' & 'H') of EACH AND EVERY row in sheet 'DATA'. For example: if BOTH the zip &&and&& name columns of row1 of sheet 'WORK' matches with ANY (any of the 5741) rows of sheet 'DATA' then row1 IS a match/hit, and the entire row1 of sheet 'WORK' is highlighted yellow to indicate the match status. If row1 of 'WORK' matches to multiple rows of 'DATA' that is okay, it is still highlighted. However if row1 of 'WORK' does not match (by the criteria of zip && name) to ANY of the 5741 rows in "DATA" then it is not highlighted and the next row (row2) is processed. Note: When comparing names, I really mean comparing the first character of the customer name fields. I do this to avoid false negatives and so not to miss a potential match. This will entail using the substring manipulation functions.(?LEFT) -------------------- HTML Code:
for EACH row in WORK { for each row in DATA { if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name)) Then Highlight current WORK.row to YELLOW, break out of current for loop ELSE next DATA row++ } next WORK row++ } *OR* HTML Code:
for each row in WORK { for each row in DATA { if ((WORK.row.column(B) == DATA.row.column(S)) && (WORK.row.firstcharof(column(C) == DATA.row.firstcharof(column(H))) Then Highlight.interior.current.WORK.row = YELLOW, break out of current for-loop ELSE next DATA row++ } next WORK row++ } CAN SOMEONE TRANSLATE THE ABOVE INTO REAL EXCEL CODE FOR ME?? Here is a link to the ENTIRE post I made (for clarity and reference): http://www.ozgrid.com/forum/showthread.php?t=49941 Here is an abridged IMAGE of the my Excel Workbook: Attachment 4703 Here is the original file I am working on (LARGE): FIRSTAM8.xls http://www4.sendthisfile.com/d.jsp?t...Oeb2puKBql1LmB Thanks, Bo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable Field items code -problems
It looks like htis last post was in error. Can someone please help with my code? I tried following the suggestions for pivotitems being made visible with autosort in manual and had no luck. I'm stuck. Thanks for any assistance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a field name in an IF function for a calculated field in a PivotTable | Excel Worksheet Functions | |||
PivotTable - How to unhide page field items? | New Users to Excel | |||
How do I count unique items in a pivottable data field. | Excel Discussion (Misc queries) | |||
Unhide items in PivotTable | Excel Programming | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming |