PDA

View Full Version : Using an IF and IN statement in VBA



phroureo
12-01-2015, 08:13 AM
Hi all,

What I want to do:


For i = 40000 to 60000
If i (is in the list of values on the filter for the pivot table)
Then .PivotItems(i).Visible = False 'uncheck the item
Else
End If
Next i



Two questions to go with this: 1) how would you do an "in" type of list?
2) Is there a way to get all the values of the filter as a list to check if i is in it?

EDIT: I have figured out how to check whether the item exists (I've pasted the code below).



For i = 40000 To 60000 FindString = i
'says that we're looking in the Account column in our table.
With Sheets("Data Import").Range("B:B") 'searches all of column B
Set FindRange = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FindRange Is Nothing Then 'if the findrange returns a value
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account")
.PivotItems(i).Visible = False
End With
Else
End If
End With
Next i


My new question: Is there a way to use a variable name in the ".PivotItems(___).Visible = False" field? When I try it, it returns an error that says "Unable to get the PivotItems property of the PivotField class"