show all rows containing a date between x and y?

N

neowok

i have some tables, each table has 9 columns and columns 6 and 7 ar
'start date' and 'finish date'.

What i want to do is let someone type in a date (in a text box) an
then have it show all the rows where the date they typed in is betwee
the start and finish date for that row.

not sure if its possible to do this with autofilter but if it is the
that would probably be the best way
 
A

aa

Hi,

you could try and use your two textboxes and then for your
autofilter text try and use ...
= textbox1
Click to expand...

or <= textbox2

luckely autofilter will allow easy manipulation!

thank you
 
B

Bob Phillips

If you input the dates in a worksheet cell, you could use Conditional
Formatting to highlight matches.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

neowok

doesnt work, maybe because the textbox is just text and the columns ar
defined as proper dates? i typed the text in the same dd-mmm-yy forma
as the dates in the columns but it still came up with nothing.

thinking about it, more what i need is the user specifies ONE date, an
it shows all rows where that date falls between the start and finis
column's dates.

so id need to run autofilter for date >= start and then run it again o
the filtered list to filter it down to <= finish, which should the
give all rows where the date specified falls between the start an
finish date on that row.

hmm
 
A

aa

Try this in your macro

Selection.AutoFilter Field:=1, Criteria1:="<date",
Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:=">date",
Operator:=xlAnd

where the criteria will relate to a textbox1...

i.e.
criteria1:=<textbox1

may work!!!

Neo
 
N

neowok

doesnt work, dont know what its doing but its listing ALL rows
regardless of what i type in the box, even if i just use one column and
remove <>=* and just autofilter with the textbox value as the criteria,
which should then just list any rows with exactly that criteria, and it
doesnt.

probably because the textbox is text, and the 2 columns are a date.
 
A

aa

why not try,

textbox1_afterupdate()

and if you are referencing to a textbox use the .value
option!!!

Neo
 
T

Tom Ogilvy

Selection.AutoFilter Field:=1, Criteria1:=">" & cdate(Textbox1.text), _
Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:="<", cDate(Textbox2.text), _
Operator:=xlAnd

cDate should respect your regional settings - VBA by itself does not - it is
US centric and will intepret a date as mm/dd/yyyy if it can.
 
D

dcstech

Try this. It will allow you to search a column and return the address of each cell that contains the search string

With Worksheets("Sheet1").Range("A1:A65536"
Set oCell = .Find(what:=TextBox1.Text, lookat:=xlPart
If Not oCell Is Nothing The
sfirst = oCell.Addres
D
Set oCell = .FindNext(oCell
Loop While Not oCell Is Nothing And oCell.Address <> sfirs
End I
End With
 
Top