Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Criteria in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Criteria in VBA
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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Criteria in VBA
Range("H1").Value = "=" & Range("A1").Value
-- __________________________________ HTH Bob "Graham H" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Criteria in VBA
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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Criteria in VBA
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 Bob Phillips wrote: Range("H1").Value = "=" & Range("A1").Value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting up a drop down box with sort criteria | Excel Discussion (Misc queries) | |||
Setting the Validation Criteria on the fly | Excel Discussion (Misc queries) | |||
setting DSUM criteria in a VBA function | Excel Programming | |||
Setting autofilter criteria | Excel Programming | |||
Q setting up UserForm for filter criteria | Excel Programming |