Excel American dates

[Duncan]

Hi there,

In relation to my post below I found out how to get it to input the
textbox date on the form into the custom results on the filter

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=">=" & Date1,
Operator:=xlAnd _
, Criteria2:="<=" & Date2

My problem is that, if for example date1 textbox is 10/04/06 it puts it
into the filter as 04/10/06!

I dont know why it does this and I am assuming that between vb and
excel there is an american/english date differance it is compensating
for, I thought about using a datepicker control instead but that would
require the user to have that control installed in excel in order to
use it and I didnt want to make it too complicated. I know that a
textbox is not the best idea for inputting a date in case the user puts
it in a differant format but at the moment the form is really simple so
that I can just work out how to do it.

Can anybody suggest anything? maybe using listboxes to choose the date
from (day * month * Year*) but then i would still have to re-work the
order to make it work and there are only 12 months!

I am so confused, this should be simple!

 

[Bob Phillips]

Try to cast the date

CDate(Date1)

etc.

You may also need to Format it, but try the CDate first.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Toppers]

Duncan,
Try

Criteria1:=">=" & Cdate(Date1) and Criteria2:=">=" & CDate(Date2)

 

[Duncan]

Thank you Bob and Toppers but nope, the cdate didnt work.

Cant figure out why................

 

[NickHK]

Duncan,
Use one of the calendar style controls ?

NickHK

 

[Toppers]

Duncan,
Declaring/formatting as shown worked for me:

Dim date1 As Date, date2 As Date
date1 = Format(TextBox1.Text, "DD/MM/yyyy")
date2 = Format(TextBox2.Text, "DD/MM/yyyy")

Sheets("sheet1").Select
Dim rng As Range

Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)
rng.Select

Range("D1").End(xlDown).Offset(1, 0).Select
ActiveCell.Select

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=">=" & CLng(date1),
Operator:=xlAnd _
, Criteria2:="<=" & CLng(date2)

HTH

 

[papou]

Hello Duncan
Toppers'last suggestion does not work for me so I gather it won't either for
you?

Dates appear in the correct format (ie dd/mm/yy) when I use this:
CriteriaOne = Format(Me.TextBox1.Value, "mm/dd/yy")
CriteriaTwo = Format(Me.TextBox2.Value, "mm/dd/yy")

A little bit confusing but it works correctly with my filter!

HTH
Cordilly
Pascal

 

[Bob Phillips]

As I said you may need to format it

Format(CDate(Date1,"dd/mm/yyyy")

The format should also be the same as the column being filtered.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Duncan]

Brilliant, Thank you Toppers.

Now I just have to work out how to add up all of collumn G from the
filtered results to display on the form and also collumn H, this is
just to show some totals on the form from what is found in the
background, I'll post my code as I am working on using the start as a
range (rng1) and the last cell (bottom one) as rng2 and then looping
offsetting from rng1 adding each time until rng1 is at rng2.

I think I am doing it all wrong! see below, (its not working!)

Set rng1 = Range("g1").Offset(1, 0)
Set rng2 = Range("g1").End(xlDown).Offset(0, 0)
Set rngtest = ActiveCell.Offset(1, 0)
rng1.Activate
Do

LOPRtot.Value = rng1 + rngtest
Loop Until rngtest.Address = rng2.Address

 

[Toppers]

Duncan,
I am not sure how you select from a filtered list so you
may be better starting another posting to get an answer from someone more
knowledgeable than me!

 

[Duncan]

Thank you Bob and Papou also, I didnt notice your postings earlier when
I replied, only noticed Toppers which worked perfectly for me.

I have started a new posting for this Toppers, Many thanks for your
help earlier. I am going to post my full sub as it stands for the
benefit of others, just ignore the do-loop as that is what im trying to
fix at the moment!

Many thanks again.

Duncan

Private Sub CommandButton1_Click()

Sheets("sheet1").Select
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)

Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Select

Dim Date1 As Date, Date2 As Date
Date1 = Format(Date1t.Text, "DD/MM/yyyy")
Date2 = Format(Date2t.Text, "DD/MM/yyyy")

Sheets("sheet1").Select

Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)

Range("D1").End(xlDown).Offset(1, 0).Select
ActiveCell.Select

Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=">=" & CLng(Date1),
Operator:=xlAnd _
, Criteria2:="<=" & CLng(Date2)

LOPRdaterange.Hide

Select Case MsgBox("Print?", vbYesNo)
Case vbYes
Sheet1.Activate

Set rng1 = Range("g1").Offset(1, 0)


rng1.Activate
'something here to check whether the cell is visible
LOPRtot.Value = ActiveCell.Value
If rng1.Offset(1, 0).Value >= 1 Then
rng1.Offset(1, 0).Activate
LOPRtot.Value = LOPRtot.Value + ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
End If

Do
If ActiveCell.Value >= "" Then
'something here to check whether the cell is visible
LOPRtot.Value = LOPRtot.Value + ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
End If
Loop Until ActiveCell.Value = ""

Selection.AutoFilter
LOPRdaterange.Show
Exit Sub

Case vbNo
Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus

Exit Sub
End Select

Sheets("sheet1").Select

End Sub