View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Finding Matching Pivot Datafield

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?

--
Regards,
Tom Ogilvy


"Todd Huttenstine" wrote in message
...
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.




-----Original Message-----
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



.