Setting Criteria in VBA

[Graham H]

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

 

[Dave Peterson]

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

 

[Bob Phillips]

Range("H1").Value = "=" & Range("A1").Value

 

[Graham H]

The value in A1 is what I was after for the criteria cell. Many thanks for the detail you
have supplied.

Graham

 

[Dave Peterson]

Sometimes it's just easier to have the sample code set up the data than trying
to explain how I tested it <bg>.

 

[Graham H]

Many thanks for your help. It is much appreciated.
However I got an interesting window I have never seen before headed "Identify Label" and
the message "There is more than one cell with this label" and Field 1 detailed. Just
intruiged as to waht is causing this.

Graham