find replace and paste special values

Hi - I wouldered if there was a way to amend the macro below or if someone may recommend a different approach if needed.

When I export to excel one of the Columns (AA) should export as numbers eg 01 - 05, 06 - 09, 10 - 49 .... ect ect but excel formats 01 - 05 as 01-May and 06 - 09 as 06 - Sep.

As there are up to 20K rows I'm looking for the most efficient way of doing this. To this end, I tried this code below but still have the same formatting issue as it doesn't find/Replace and replace with "paste Special Values"

Has someone got a macro for this?

Many thanks

Please Login or Register  to view this content.

Find Replace
01-May 01-05
06-Sep 06-09
Oct-49 10-49
01-May 01-05

Hi!

Can't you format the AA column as text BEFORE pasteing the data?

Please Login or Register  to view this content.

my 2c

Multsumesc for you input and thanks for taking the time to help! however, this resulted in a run time error 13 type mismatch..

Please Login or Register  to view this content.

Hi - Thanks for your response. I think id be better off posting a sample! I'll do it from work tomorrow. Cheers mate for response.

Hi again,

I was trying to avoid the effect of excel reading as date format instead of number/text format so you won't be need to use the multiFindNReplace() Sub.
If the column AA was formated as text BEFORE any values are inserted in, the values would not be converted to date.
So before inserting values into column AA you should try and formating it as text with the following code:

Please Login or Register  to view this content.

Hey Guys, cant wait to get to work tomorrow to see if i can sort this issue! thanks both for your help. I'll be sure to feedback the outcome.

@ Bulina - i've been to Romania many times to play in badminton tournaments, Iasi, Tulcea, Bacau, Bucharest... love mitch and sarmarle! There is a great restaurant called La Mamma in Bucherest which serves traditional food you should check it out!

Right - I'm at work ! going to test this !!

Hi, just to feedback - thanks AB, i added this line to the code

Please Login or Register  to view this content.

and boom - done and dusted!

Many thanks both for taking the time to assist! perfect!