Code not working

[Maxi]

I have dates in range G7:N7 in the format d-mmm but the below code
doesn't go inside the "Do something" state, it says Date not found even
if I put in a date which is present in the G7:N7 range.

Can anybody help?

Sub foo()
Dim ws1 As Worksheet
Dim dDt As String

Set ws1 = Sheets("Sheet1")
dDt = Application.InputBox("Enter Date", "Schedule Date", "m/d/yyyy")
Application.ScreenUpdating = False
If dDt = vbNullString Then
Exit Sub
End If
dDt = Format(dDt, "d-mmm")
If Application.WorksheetFunction.CountIf(ws1.Rows(7), dDt) > 0 Then

 

[Susan]

i think you have to dim dDt as date
it's looking for a date, but you've got it dimmed as a string.
that might help.
susan

 

[Maxi]

For some reason I have to dim it to string. Is there any change
required in the line so that the dDt variable is recognized as date
inteast of string but still keeping it as string:
If Application.WorksheetFunction.CountIf(ws1.Rows(7), dDt) > 0 Then

something like datevalue(dDt). Not sure how the syntax be.

Thanx

 

[Susan]

i searched the newsgroup with "force string into date" & came up with
these links:

title: changing a string date into a 'date'
http://groups.google.com/group/micr...tring+into+date&rnum=1&hl=en#407f93e74653ce57

title: paste HTML with date different in VBA from manual, format error
http://groups.google.com/group/micr...tring+into+date&rnum=2&hl=en#cc11a49132ad9a44

title: Date headaches
http://groups.google.com/group/micr...tring+into+date&rnum=5&hl=en#0b7996f610f306c3

they might get you going.
susan