Using a macro to control the autofilter function

A

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
 
B

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top