Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Matching Pivot Datafield
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Matching Pivot Datafield
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Matching Pivot Datafield
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 -----Original Message----- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Matching Pivot Datafield
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 -- Regards, Tom Ogilvy "Todd Huttenstine" wrote in message ... 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 -----Original Message----- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding matching cells | Excel Discussion (Misc queries) | |||
Not finding matching value in vlookup | Excel Worksheet Functions | |||
Finding matching values | Excel Worksheet Functions | |||
Can I pull field as datafield or as a output from a pivot table re | Excel Discussion (Misc queries) | |||
Finding One Value, Matching Three Criteria | Excel Discussion (Misc queries) |