Finding Matching Pivot Datafield
Are you trying to match the pivotfield name or the value in the TrmVal
variable. Rigth now, you are using the pivotfield name. I made a few
changes to the code including dim'ing j as Range.
Dim PvtTable
Dim TrmVal
Dim i As Long
Dim j As Range
'Clears all checks from Listbox1
With Worksheets("Wkly Renewals").ListBox1
For i = 0 To .ListCount - 1
.Selected(i) = False
Next i
End With
Set PvtTable = Worksheets("Wkly Renewals").PivotTables("PivotTable4")
For Each pvtfield In PvtTable.DataFields
TrmVal = Trim(Mid(pvtfield, 6))
With Worksheets("Wkly Renewals").ListBox1
For i = 0 To .ListCount - 1
On Error Resume Next
set j = Sheets("Wkly Renewals") _
.Range("E8:BV8").Find(pvtfield)
If not j is nothing then .Selected(i) = True
set j = Nothing
Next i
End With
Next pvtfield
--
Regards,
Tom Ogilvy
"Todd Huttenstine" wrote in message
...
Below is a code that I cannot get to work. I am trying to
make it do the following: For each datafield it finds in
the pivot field I want it to Trim the first 6 letters to
the left off and then store that value as a variable
called TrmVal. This works so far. Then I want it to look
in all the items in the listbox1 and when it finds a
match, it needs to put a check in that listbox item. Its
not working.
Dim PvtTable
Dim TrmVal
Dim i As Long
Dim j As Long
'Clears all checks from Listbox1
With Worksheets("Wkly Renewals").ListBox1
For i = 0 To .ListCount - 1
.Selected(i) = False
Next i
End With
Set PvtTable = Worksheets("Wkly Renewals").PivotTables
("PivotTable4")
For Each pvtfield In PvtTable.DataFields
TrmVal = Trim(Mid(pvtfield, 6))
With Worksheets("Wkly Renewals").ListBox1
For i = 0 To .ListCount - 1
On Error Resume Next
j = Sheets("Wkly Renewals").Range
("E8:BV8").Find(pvtfield)
If j 0 Then .Selected(i) = True
j = 0
Next i
End With
Next pvtfield
|