Find and Replace Recorded Macro Not Working

Hello,

I am trying to find and replace various instances of dates to text. An example would be replacing 01/03/2005 with MAR5.

I have recorded this basic macro:

Please Login or Register  to view this content.

When I run it though all it does is select column A and nothing else.

Is there something I'm missing - it worked when I recorded it.

Many Thanks,

James

Hi there, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

then it should work.

Thanks - this has made the macro work if I run it from VB, but if I try to run it from the workbook it still just selects column A:A.

Not the end of the world - it's still quicker then manually finding and replacing!

Try this instead:

Please Login or Register  to view this content.

Thanks, but this doesn't seem to work.

Originally Posted by S O

Try this instead:

Please Login or Register  to view this content.

What doesn't work? It could be a known issue with parsing dates as a string - dates are stored as a numeric value in Excel and are only displayed as a string. Can you upload an example of your data?

Basically I have to export files as CSV from a database that we use. We have various entities which use three letters and then a number as references, which leads to JUL1, MAR1 etc coming through as Jul-01 (01/07/2001), Mar-01 (01/03/2001) etc. I could save the file to disk, then import and set the column as text in order to stop this happening from the start, however, for some reason when I save the file rather then just directly opening it up in excel there seem to be extra rows which mess up a much more complicated macro that I use on these reports, which really don't want to have to re-write/record!

A stated above for some reason the galvinpaddy fix makes this work if I run the macro in Visual Basic, however, not from the workbook itself. When I run your option nothing seems to happen at all!

In the sample the data is in column B, but when I tested your macro I moved it to column A.

Thanks for your help!

Sample 2.xlsx

Do you have any control on how the CSV is created? Simply adding quotation marks around the references would stop Excel parsing it as a date.

e.g. instead of:

Please Login or Register  to view this content.

change to:

Please Login or Register  to view this content.

If you have an example of the CSV file I could write a quick "conversion" script to do that for you

Thanks S O, but if I save the CSV file to disc and then import it, it somehow changes the file from the way it is if I simply select "Export to Application". Not sure why - tbh I'm not a massive fan of the PMS system we use but I'm not planning on stumping up the cash to change it!

It seems for now the easiest thing is for me to just run my macro from visual basic until I figure out why it doesn't like to run the whole thing from the workbook.

Thank you for taking the time to help me with this - I really appreciate it.

James

After scouring the internet I have found this:

Please Login or Register  to view this content.

Interestingly enough this seems to work, although if you run it a second time all of MAR5 cells now become 05/03/2015 - but it does work the first time. I have been trying to add more options (as there are a few things that need to be replaced - 01/03/2001, 01/03/2004, 01/08/2001, 01/11/2001) but this doesn't seem to be easy to do.

I'm just wondering why when I record the macro through mouse navigation excel can't seem to make it work.