Pivottable Field items code -problems

[Guest]

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
can’t figure out why. Any ideas?
Thanks!

 

[Guest]

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
can’t figure out why. Any ideas?
Thanks!

Could someone HELP TRANSLATE this simple psuedo code into MACRO please?!

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]

(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)

--------------------

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*

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[/QUOTE] THE ABOVE
INTO REAL EXCEL CODE FOR ME??[/QUOTE]



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:
View attachment 4703

Here is the original file I am working on (LARGE):
FIRSTAM8.xls
http://www4.sendthisfile.com/d.jsp?t=yACWjvbmi7Oeb2puKBql1LmB

Thanks,
Bo

 

[Guest]

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