Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofilter criteria
I have a macro with this code:
Dim i As Long Dim BMP As Variant Dim EMP As Variant Dim RSE As String * 25 For i = 3 To 3 RSE = Worksheets(1).Range("F" & i).Value BMP = Worksheets(1).Range("D" & i).Value EMP = Worksheets(1).Range("E" & i).Value Sheets("TSTF").Select Selection.AutoFilter Field:=1, Criteria1:=RSE Selection.AutoFilter Field:=2, Criteria1:="=BMP", Operator:=xlAnd, _ Criteria2:="<=EMP" Range("A2:M13806").Select Selection.Copy etc. etc. BMP and EMP are reading numbers like 2.57 and 5.14. If i put those numbers where the variables are in the critereria code, it works. Why won't it work with the variables? RSE is working but not BMP and EMP. thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofilter criteria
Criteria1:="=BMP" is literally looking for greater or equal to the letters
B M P. You need Criteria1:="" & BMP similarly for <=EMP -- Criteria2:="<=" & EMP Bob Umlas Excel MVP "Knox" wrote in message ... I have a macro with this code: Dim i As Long Dim BMP As Variant Dim EMP As Variant Dim RSE As String * 25 For i = 3 To 3 RSE = Worksheets(1).Range("F" & i).Value BMP = Worksheets(1).Range("D" & i).Value EMP = Worksheets(1).Range("E" & i).Value Sheets("TSTF").Select Selection.AutoFilter Field:=1, Criteria1:=RSE Selection.AutoFilter Field:=2, Criteria1:="=BMP", Operator:=xlAnd, _ Criteria2:="<=EMP" Range("A2:M13806").Select Selection.Copy etc. etc. BMP and EMP are reading numbers like 2.57 and 5.14. If i put those numbers where the variables are in the critereria code, it works. Why won't it work with the variables? RSE is working but not BMP and EMP. thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofilter criteria
Thank you, that worked! Why does RSE work one way and BMP and EMP work a
different way? "Bob Umlas" wrote: Criteria1:="=BMP" is literally looking for greater or equal to the letters B M P. You need Criteria1:="" & BMP similarly for <=EMP -- Criteria2:="<=" & EMP Bob Umlas Excel MVP "Knox" wrote in message ... I have a macro with this code: Dim i As Long Dim BMP As Variant Dim EMP As Variant Dim RSE As String * 25 For i = 3 To 3 RSE = Worksheets(1).Range("F" & i).Value BMP = Worksheets(1).Range("D" & i).Value EMP = Worksheets(1).Range("E" & i).Value Sheets("TSTF").Select Selection.AutoFilter Field:=1, Criteria1:=RSE Selection.AutoFilter Field:=2, Criteria1:="=BMP", Operator:=xlAnd, _ Criteria2:="<=EMP" Range("A2:M13806").Select Selection.Copy etc. etc. BMP and EMP are reading numbers like 2.57 and 5.14. If i put those numbers where the variables are in the critereria code, it works. Why won't it work with the variables? RSE is working but not BMP and EMP. thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofilter criteria
This line:
Selection.AutoFilter Field:=1, Criteria1:=RSE is treating RSE as a variable. If you had used similar syntax, you would have had the same kind of problem: Selection.AutoFilter Field:=1, Criteria1:="=RSE" (would fail) Selection.AutoFilter Field:=1, Criteria1:="=" & RSE (would work) Knox wrote: Thank you, that worked! Why does RSE work one way and BMP and EMP work a different way? "Bob Umlas" wrote: Criteria1:="=BMP" is literally looking for greater or equal to the letters B M P. You need Criteria1:="" & BMP similarly for <=EMP -- Criteria2:="<=" & EMP Bob Umlas Excel MVP "Knox" wrote in message ... I have a macro with this code: Dim i As Long Dim BMP As Variant Dim EMP As Variant Dim RSE As String * 25 For i = 3 To 3 RSE = Worksheets(1).Range("F" & i).Value BMP = Worksheets(1).Range("D" & i).Value EMP = Worksheets(1).Range("E" & i).Value Sheets("TSTF").Select Selection.AutoFilter Field:=1, Criteria1:=RSE Selection.AutoFilter Field:=2, Criteria1:="=BMP", Operator:=xlAnd, _ Criteria2:="<=EMP" Range("A2:M13806").Select Selection.Copy etc. etc. BMP and EMP are reading numbers like 2.57 and 5.14. If i put those numbers where the variables are in the critereria code, it works. Why won't it work with the variables? RSE is working but not BMP and EMP. thank you -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
Autofilter criteria not yet known | Excel Programming | |||
AutoFilter criteria | Excel Programming | |||
AutoFilter Criteria VBA | Excel Programming | |||
VBA Autofilter Criteria | Excel Programming |