Find date in column then delete row???

[Guest]

I have a spreadsheet with a date in cell S5

I want the macro to look down cells S10:S300 and see if the date in S5 is in
any of the columns in S10-A300. If it is I want the found row to be deleted.

I have used this code but it is not working and keeps saying nothing is found.

Dim FindString As String
Dim rng As Range


FindString = Range("s5").Value

If FindString <> "" Then
With Sheets("Sheet1").Range("s9:s500")
Set rng = .Find(What:=FindString, _
After:=.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End If

Can anyone help? Many thanks.

 

[Dave Peterson]

Sometimes, converting the date to a long helps:

Set rng = .Find(What:=FindString, _
becomes:
Set rng = .Find(What:=clng(FindString), _

 

[Guest]

Thanks Dave but this doesnt work...it generates a Type Mismatch error???

 

[Tom Ogilvy]

try doing

set rng = .find(What:=clng(cdate(FindString)), _

 

[Dave Peterson]

If that causes an error, then it sounds like s5 doesn't really contain a date.

if isdate(findstring) = false then
msgbox findstring
end if

If it's text that looks like a date, maybe:

clng(cdate(findstring))

???

 

[Guest]

I have a vlookup formula in cell s5 which looks up a date. Toms change
doesnt give any errors but it still doesnt find the date from S9 - S300????
I am lost?

Cheers

SD