Finding Matching Pivot Datafield

[Todd Huttenstine]

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

 

[Tom Ogilvy]

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

 

[Todd Huttenstine]

I am trying to match the datafield in the pivottable with
the items in the listbox. Because the actual name of the
datafield will not match any item in the listbox, I had to
change the value so I created a variable called TrmVal
which is only the modified datafield name value and that
is the value I am trying to match with the values in the
listbox. When TrmVal matches an item in the listbox, the
item in the listbox needs to get checked.

The code you gave me is giving me the same result as all
my other attempts, it does nothing.

 

[Tom Ogilvy]

I doubt the limitation is any changes I suggested to your code code. They
were all pertinent to what could be garnered from your meager attempts. It
is unclear why you are trying to use find on a range if you want to check
the listbox - that relation is known only to you. I assumed there was some
reason to set trmVal, but you never use it, so that remains a mystery as
well. You can rest assured, that if I had to solve this problem, I wouldn't
need to involve you in a discussion of what approach to use. Here is a
similar question to yours which you might be able to provide some assistance
on. I am thinking of a number between 1 and 100 (inclusive). Can you guess
what it is?

 

[Todd Huttenstine]

Tom

lol were you thinking the number 72??? Sorry I hope you
didnt take my last post the wrong way. I was not
criticizing your help at all. I was looking back over the
code and the range indeed has no use so I took it out. I
had a bite to eat and then came back and got it working.
Here is what I was trying to do. See below:

'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
'Value from the datafield of the pivottable
TrmVal = Trim(Mid(pvtfield, 6))
With Worksheets("Wkly Renewals").ListBox1
For i = 0 To .ListCount - 1
On Error Resume Next
'Vlaue from the listbox
ListValue = .List(i)
' j = WorksheetFunction.Match(ListValue,
TrmVal, 0)
' If j Is Nothing Then
If ListValue = TrmVal Then
.Selected(i) = True
Else
End If
Set j = Nothing
Next
End With
Next

 

[Tom Ogilvy]

Set PvtTable = Worksheets("Wkly Renewals") _
.PivotTables("PivotTable4")
For Each pvtfield In PvtTable.DataFields
'Value from the datafield of the pivottable
TrmVal = Trim(Mid(pvtfield, 6))
With Worksheets("Wkly Renewals").ListBox1
For i = 0 To .ListCount - 1
If lcase(.List(i)) = lcase(TrmVal) Then
.Selected(i) = True
Exit For
End If
Next
End With
Next