Find date in column then delete row???
 

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.

Find date in column then delete row???
 

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

Find date in column then delete row???
 

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

Find date in column then delete row???
 

try doing

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

--
Regards,
Tom Ogilvy


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

Find date in column then delete row???
 

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

Find date in column then delete row???
 

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

Find date in column then delete row???
 

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

Find date in column then delete row???
 

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