Hi,
I need to paste data into an excel sheet from access, and then sort b
date field. However when the date field is entered by paste functio
into an excel cell the formula from access is retained which prevent
searching on the date.
To solve this manually, I need to edit the cell (by F2 seems th
easiest) and then enter. This changes the formula into a searchabl
date.

My options are to either work out how to do the manual process above o
work out how to remove the formuala (I have tried copy and paste specia
as 'values' which didn't work)
If you can offer advice on how to create a loop via a macro to edi
each cell in a column and then 'enter/return' this would do it. I ca
create the macro, but what I don't know is how to code the loop t
select the next cell.....

Ton

--
Tony777

-----------------------------------------------------------------------
Tony7777's Profile: http://www.excelforum.com/member.php...fo&userid=1525
View this thread: http://www.excelforum.com/showthread.php?threadid=26883

if your dates have a slash ( 10/20/2004), then you don't need a macro.

select the column, Format as Date, then do
Edit=Replace
What \
With \

and this should convert them to dates unless excel can not recognize them as
dates.

If you need a macro, turn on the macro recorder while you do it.

Another method is to select the column and do Data=Text to columns, select
delimited and pick a delimiter not found in any of the cells.


--
Regards,
Tom Ogilvy


"Tony7777" wrote in message
...

Hi,
I need to paste data into an excel sheet from access, and then sort by
date field. However when the date field is entered by paste function
into an excel cell the formula from access is retained which prevents
searching on the date.
To solve this manually, I need to edit the cell (by F2 seems the
easiest) and then enter. This changes the formula into a searchable
date.

My options are to either work out how to do the manual process above or
work out how to remove the formuala (I have tried copy and paste special
as 'values' which didn't work)
If you can offer advice on how to create a loop via a macro to edit
each cell in a column and then 'enter/return' this would do it. I can
create the macro, but what I don't know is how to code the loop to
select the next cell.....

Tony


--
Tony7777


------------------------------------------------------------------------
Tony7777's Profile:
http://www.excelforum.com/member.php...o&userid=15257
View this thread: http://www.excelforum.com/showthread...hreadid=268830

hi,
Access table don't have formula so i assume that you are
getting your data from a query which can have formaula.
(but that is just in the header.) Hmmmmmm.
Instead of copy and paste, i might suggest that you set up
a MSQ (microsoft query) Toolsget exteranl data new query.
use the Access query as your data source for the MSQ.
And i think that will solve your date problem.
works for me.
Reqards
Frank

-----Original Message-----

Hi,
I need to paste data into an excel sheet from access, and
then sort by
date field. However when the date field is entered by
paste function
into an excel cell the formula from access is retained
which prevents
searching on the date.
To solve this manually, I need to edit the cell (by F2
seems the
easiest) and then enter. This changes the formula into a
searchable
date.

My options are to either work out how to do the manual
process above or
work out how to remove the formuala (I have tried copy
and paste special
as 'values' which didn't work)
If you can offer advice on how to create a loop via a
macro to edit
each cell in a column and then 'enter/return' this would
do it. I can
create the macro, but what I don't know is how to code
the loop to
select the next cell.....

Tony


--
Tony7777


----------------------------------------------------------
--------------
Tony7777's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=15257
View this thread:
http://www.excelforum.com/showthread...hreadid=268830

.