Using a macro to control the autofilter function

[APealin]

I am trying to write a macro which filters a table of data usin
criteria determined in another worksheet.

This is the code that I produced to date. The variables Cruiseline
and cruiseline2 work fine however I want to be able to filter a dat
range as well. I cannot get the greater than equals to command t
work.

Any suggestions?

Dim Cruiseline1
Dim Cruiseline2
Dim Date1
Dim Date2

Cruiseline1 = Worksheets("Front Sheet").Range("b3").Value
Cruiseline2 = Worksheets("Front Sheet").Range("b4").Value
Date1 = Worksheets("Front Sheet").Range("c6").Value
Date2 = Worksheets("Front Sheet").Range("c7").Value

Selection.AutoFilter Field:=5, Criteria1:=Cruiseline1, _
Operator:=xlOr, Criteria2:=Cruiseline2

Selection.AutoFilter Field:=1, Criteria1:>=Format(Date1
Selection.Cells(2, 1).NumberFormat), _
Operator:=xlAnd, Criteria2:<=Format(Date2, Selection.Cells(2
1).NumberFormat

 

[Bernie Deitrick]

APealin,

Remember, the macro recorder is your friend for helping figure out the correct syntax....

Selection.AutoFilter Field:=1, Criteria1:=">=" & Format(Date1, Selection.Cells(2, 1).NumberFormat),
_
Operator:=xlAnd, Criteria2:="<=" & Format(Date2, Selection.Cells(2, 1).NumberFormat)


HTH,
Bernie
MS Excel MVP

 

[Bob Phillips]

Dim Cruiseline1
Dim Cruiseline2
Dim Date1
Dim Date2

Cruiseline1 = Worksheets("Front Sheet").Range("b3").Value
Cruiseline2 = Worksheets("Front Sheet").Range("b4").Value
Date1 = Worksheets("Front Sheet").Range("c6").Value
Date2 = Worksheets("Front Sheet").Range("c7").Value

Selection.AutoFilter Field:=5, _
Criteria1:=Cruiseline1, _
Operator:=xlOr, _
Criteria2:=Cruiseline2

With Selection
.AutoFilter Field:=1, _
Criteria1:=">=" & Format(Date1, .Cells(2, 1).NumberFormat),
_
Operator:=xlAnd, _
Criteria2:="<=" & Format(Date2, .Cells(2, 1).NumberFormat)
End With


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)