Setting Criteria in VBA
The value in A1 is what I was after for the criteria cell. Many thanks for the detail you
have supplied.
Graham
Dave Peterson wrote:
Do you want to use the value from A1 in that criteria cell or do you want to use
a formula that points at that cell?
Option Explicit
Sub testme()
With Worksheets.Add
'my criteria
.Range("A1").Value = "A"
'headers for testdata
.Range("C1").Value = "asdf"
'test data
.Range("C2:C6").Value _
= Application.Transpose(Array("a", "B", "Abc", "b", "A"))
'header for criteria range
.Range("E1").Value = .Range("C1").Value
'criteria range
'using a formula that still points to A1
'like ="="&A1
.Range("e2").Formula = "=""=""&A1"
'or this, but not both...
'using a formula that uses the value in A1
'like ="=A"
.Range("e2").Formula = "=""=" & .Range("a1").Value & """"
'do the advanced filter
.Range("C1:C6").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=.Range("e1:e2"), _
Unique:=False
End With
End Sub
Graham H wrote:
I have to set criteria for an advanced filter action in a particular way because of the
nature of the data. If I have Field 1 for example as the citeria I have to enter this as
="=Field 1" and it selects the correct data perfectly and I am grateful to Dave Peterson
for making me aware of this. I thought I was on a roll then but hit a wall when I tried to
enter this in a procedure. If Field 1 is in A1 and I want to put the correct format of
criteria reference in cell H1 using VBA, can this be done or do I have to rethink the
filtering concept? I would value any help.
Graham
|