date format

[duncan.mutchblache]

Assuming xl2000 or later:

Dim s1 as String, s2 as String
s1 = Date1t.text
s2 = Date2t.text
s1 = Replace(s1,".","/")
s2 = Replace(s2,".","/")


if isdate(s1) then
Date1 = cdate(s1)
else
msgbox "Bad date"
Date1t.Text = ""
exit sub
end if
if isdate(s2) then
Date2 = cdate(s2)
else
msgbox "Bad Date"
Date2t.Text
exit sub
End if

Tom,

For some reason I cannot get this to work, dont know why? ive run
through it with F8 and it goes through the code but doesnt replace
anything.

Any ideas what might be wrong?

Duncan

 

[Guest]

Duncan,
... butting in ...I tried the code and it works fine
for me (as I expected!)

Are you saying a date like 20.04.06 isn't converted to 20/04/06? i.e. the
Replace is not working?

 

[Duncan]

aye!

maybe its me....ill try it again..

 

[Duncan]

(I didnt think for one second that Toms code would be wrong, I know
there is something wrong with how im applying it but cant figure it
out........)

 

[Duncan]

Ok ok, spank me now.

Im gonna paste my code for the benefit of other but it was me being
absolutely stupid.
All I needed to do was show in the textboxes that it had actually
changed

date1t.text = s1
date2t.text = s2

I only found that out when I skipped further and found that it did the
filter ok, then saw that it just wasnt showing me what it was putting
into the filter. (Thank you Toppers...again!)

Many apologies Tom. (full code is below)

(I wonder if it matters that i have used the name s2 already for
something further below?...more testing required)



Private Sub CommandButton1_Click()

Sheets("sheet1").Select

' the below is to replace dots with dashes, but doesnt work yet
Dim s1 As String, s2 As String
s1 = Date1t.Text
s2 = Date2t.Text
s1 = Replace(s1, ".", "/")
s2 = Replace(s2, ".", "/")
If IsDate(s1) Then
Date1 = CDate(s1)
Else
MsgBox "Bad date"
Date1t.Text = ""
Exit Sub
End If
If IsDate(s2) Then
Date2 = CDate(s2)
Else
MsgBox "Bad Date"
Date2t.Text = ""
Exit Sub
End If

Date1t.Text = s1
Date2t.Text = s2

If Date1t.Value <= "" Then
Exit Sub
End If
If Date2t.Value <= "" Then
Exit Sub
End If

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 rng = Range("A1:I" & Range("a65536").End(xlUp).Row)
rng.Select
Selection.PrintOut Copies:=1, Collate:=True


With Worksheets("Sheet1")
Set rng = Intersect(.AutoFilter.Range, .Columns(7))
s = Application.Subtotal(9, rng)
LOPRtot.Value = s
End With


With Worksheets("Sheet1")
Set rng = Intersect(.AutoFilter.Range, .Columns(8))
s2 = Application.Subtotal(9, rng)
LOPRus.Value = s2
End With

Selection.AutoFilter
LOPRdaterange.Show
Exit Sub

Case vbNo

With Worksheets("Sheet1")
Lastrow = .Cells(Rows.Count, "G").End(xlUp).Row
s = 0
For i = 2 To Lastrow
If .Rows(i).Hidden = False Then s = s + .Cells(i, "G")
Next i
End With
LOPRtot.Value = s

With Worksheets("Sheet1")
Lastrow2 = .Cells(Rows.Count, "H").End(xlUp).Row
s2 = 0
For j = 2 To Lastrow2
If .Rows(j).Hidden = False Then s2 = s2 + .Cells(j, "H")
Next j
End With
LOPRus.Value = s2

Selection.AutoFilter
LOPRdaterange.Show

Exit Sub
End Select


Sheets("sheet1").Select

End Sub

 

[Guest]

If you want, post your w/book with note on sample inputs and I'll look at it.

([email protected])