About Us

SD

Finally works thanks Tom and Dave!

"Tom Ogilvy" wrote:

set rng = .find(What:=format(cdate(FindString),Range("S9").N umberformat), _

Personally I prefer match

Dim res as Variant
res = Application.Match(clng(cdate(FindString)),Range("S 9:S300"),0)
if not iserror(res) then
msgbox "found at row " & Range("S9:S300")(res).Address
Else
msgbox "Not found"
End if

--
Regards,
Tom Ogilvy

"SD" wrote in message
...
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

"Dave Peterson" wrote:

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))

???

SD wrote:

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

"Dave Peterson" wrote:

Sometimes, converting the date to a long helps:

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

SD wrote:

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

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find & Delete columns to the left stopping at column B	Aria[_2_]	Excel Programming	11	May 20th 06 01:48 AM
Find & Delete columns to the left stopping at column B	Aria[_2_]	Excel Programming	0	May 18th 06 06:48 PM
Find Column heading and then Delete entire column	Kobayashi[_58_]	Excel Programming	4	October 17th 05 09:09 PM
Find and Delete data in a column	rahul_chatterjee[_2_]	Excel Programming	4	October 25th 04 11:08 PM
macro to find something in column A and delete 5 rows below it	vikram	Excel Programming	1	April 30th 04 12:04 PM

Menu

About Us