I have date in range a1 to a2000
On only one line there will be a date. Is there a simplecommand that can find this date. At present i am using the following code.


Set CHECKRANGE = Columns(1).Cells
For Each CELL In CHECKRANGE
If CELL.Text Like "*##/##/20##*" Then
........
.........
NEXT CELL

an example of the date in column is " 146.01 153.80 n/a 0.06 -1.85 ( -1.3%) n/a 03/03/2006 0 £0.00 £0.00 n/a "
i need to find and extract that date.


Thanks

you can use vlookup
If that doesn't work check out data, filter
I am not sure what you are doing with this date, are you just looking
to see if its there, or are you going to extract data from the column
beside it


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=519109

A slight modification of your code:

Dim v As Variant
Set CHECKRANGE = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cell In CHECKRANGE
v = Split(cell.Text, " ")
For i = LBound(v) To UBound(v)
If v(i) Like "*##/##/20##*" Then
MsgBox v(i)
GoTo Nextaction:
End If
Next i
Next cell
Nextaction:

HTH

"Sunil Patel" wrote:

I have date in range a1 to a2000
On only one line there will be a date. Is there a simplecommand that can find this date. At present i am using the following code.


Set CHECKRANGE = Columns(1).Cells
For Each CELL In CHECKRANGE
If CELL.Text Like "*##/##/20##*" Then
........
.........
NEXT CELL

an example of the date in column is " 146.01 153.80 n/a 0.06 -1.85 ( -1.3%) n/a 03/03/2006 0 £0.00 £0.00 n/a "
i need to find and extract that date.


Thanks

One way:

Dim rCell As Range
Dim sExtract As String
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If .Text Like "*##/##/20##*" Then
sExtract = Mid(.Text, InStr(.Text, "/20") - 5, 10)
MsgBox .Address(False, False) & ": " & sExtract
End If
End With
Next rCell

Note: this is based on your example. If you had data like:

146.01 153.80 n/a 0.06 1/20 (-1.3%) n/a 03/03/2006 0 ...

this would need more sophisticated pattern matching


In article ,
"Sunil Patel" wrote:

I have date in range a1 to a2000
On only one line there will be a date. Is there a simplecommand that can find
this date. At present i am using the following code.


Set CHECKRANGE = Columns(1).Cells
For Each CELL In CHECKRANGE
If CELL.Text Like "*##/##/20##*" Then
.......
........
NEXT CELL

an example of the date in column is " 146.01 153.80 n/a 0.06 -1.85 (
-1.3%) n/a 03/03/2006 0 £0.00 £0.00 n/a "
i need to find and extract that date.


Thanks