Syntax / Compile error

[applebyd]

Hi all,

Havethe following which works fine as a formula in the workbook:

Range("AD2") = "=DATEVALUE(TRIM(MID(AE2,FIND(" ",AE2),3))&" "&LEFT(AE2,FIND(" ",AE2)-1)&" "&TRIM(MID(AE2,FIND(" ",AE2,FIND(" ",AE2)+1),5)))"

However, if I try and use it in VBA I am getting a syntax error (expected End of Statement) highlighted in the section ,AE2),3))&".

Not sure what is happening as if I put it in the workbook I can copy down quite happily. Just not when trying to do it programmatically.

My head hurts

Any help appreciated.

Thanks

DaveA

 

[applebyd]

Ok, it works perfectly and does away with the cumbersome Final_Tidy Sub.

Now tell me why!

Many many thanks

Regards

 

[Norie]

DaveA

When you use things like Select/Selection in code you can't always be sure what ranges/sheets etc are being referred to.

In the code I posted the range references are explicit so, in theory anyway, the code should be applied to the correct ranges.