excel2000 pivottable macro
 

how to build a macro that will choose from column filed
automaticly a subject which is written also in a cell like A1
example:
the option in the column filed are "haim", "yael", "yoav"
cell A1 contains the name "yael"
the i want that the macro will choose the name "yael"

thank you
rozent

excel2000 pivottable macro
 

assume the options are listed in C1:C3 then

res = Application.Match(Range("A1").Value,Range("C1:C3") ,0)
if not iserror(res) then
msgbox Cells(res,3).Address & " contains " & Range("A1").Value
else
msgbox "Not found
End if

--
Regards,
Tom Ogilvy

"?????" wrote in message
...
how to build a macro that will choose from column filed
automaticly a subject which is written also in a cell like A1
example:
the option in the column filed are "haim", "yael", "yoav"
cell A1 contains the name "yael"
the i want that the macro will choose the name "yael"

thank you
rozent

excel2000 pivottable macro
 

hi tom
i think you did not get that the column filed is
blong to pivottable
i can choose from this column by a botton
the macro must make the choosen acording to
the content in A1

thank you
rozent

"Tom Ogilvy" wrote:

assume the options are listed in C1:C3 then

res = Application.Match(Range("A1").Value,Range("C1:C3") ,0)
if not iserror(res) then
msgbox Cells(res,3).Address & " contains " & Range("A1").Value
else
msgbox "Not found
End if

--
Regards,
Tom Ogilvy

"?????" wrote in message
...
how to build a macro that will choose from column filed
automaticly a subject which is written also in a cell like A1
example:
the option in the column filed are "haim", "yael", "yoav"
cell A1 contains the name "yael"
the i want that the macro will choose the name "yael"

thank you
rozent

excel2000 pivottable macro
 

No, I usually look for the question in the body of the message. If you put
critical information in the subject and so forth, then it may be read or
not. I assume these 3 possibilities are pivotitems for the column Field
named Field1

Sub ABCD()
Dim pvtFld As PivotField
Dim pvtItem As PivotItem

Set pvtFld = ActiveSheet.PivotTables("PivotTable1").ColumnField s("Field1")
For Each pvtItem In pvtFld.PivotItems
pvtItem.Visible = True
If pvtItem.Name < Range("A1").Value Then
pvtItem.Visible = False
End If
Next

End Sub

The above worked for me.

--
Regards,
Tom Ogilvy



"?????" wrote in message
...
hi tom
i think you did not get that the column filed is
blong to pivottable
i can choose from this column by a botton
the macro must make the choosen acording to
the content in A1

thank you
rozent

"Tom Ogilvy" wrote:

assume the options are listed in C1:C3 then

res = Application.Match(Range("A1").Value,Range("C1:C3") ,0)
if not iserror(res) then
msgbox Cells(res,3).Address & " contains " & Range("A1").Value
else
msgbox "Not found
End if

--
Regards,
Tom Ogilvy

"?????" wrote in message
...
how to build a macro that will choose from column filed
automaticly a subject which is written also in a cell like A1
example:
the option in the column filed are "haim", "yael", "yoav"
cell A1 contains the name "yael"
the i want that the macro will choose the name "yael"

thank you
rozent

excel2000 pivottable macro
 

That first one could have problem base on state of the pivottable when run.
This is a little more robust although not very efficient.

Sub ABCD()
Dim pvtFld As PivotField
Dim pvtItem As PivotItem

Set pvtFld = ActiveSheet.PivotTables("PivotTable1").ColumnField s("Field1")
For Each pvtItem In pvtFld.PivotItems
pvtItem.Visible = True
Next
For Each pvtItem In pvtFld.PivotItems
If pvtItem.Name < Range("A1").Value Then
pvtItem.Visible = False
End If
Next

End Sub


--
Regards,
Tom Ogilvy

"?????" wrote in message
...
hi tom
i think you did not get that the column filed is
blong to pivottable
i can choose from this column by a botton
the macro must make the choosen acording to
the content in A1

thank you
rozent

"Tom Ogilvy" wrote:

assume the options are listed in C1:C3 then

res = Application.Match(Range("A1").Value,Range("C1:C3") ,0)
if not iserror(res) then
msgbox Cells(res,3).Address & " contains " & Range("A1").Value
else
msgbox "Not found
End if

--
Regards,
Tom Ogilvy

"?????" wrote in message
...
how to build a macro that will choose from column filed
automaticly a subject which is written also in a cell like A1
example:
the option in the column filed are "haim", "yael", "yoav"
cell A1 contains the name "yael"
the i want that the macro will choose the name "yael"

thank you
rozent