Copy/Paste

[James_C]

Hi people,
I'd like help writing a macro please...
So basically I need it to copy say "6141GMPV" from A1 to B1 - thats easy
The hard part is if the last 2 characters in the copied cell are any of these ("PV" "PR" "WV" "WR" "TV/CV" "AL" "Other" "TT") then I'd like them to be removed and paste "6141GM" into B2 and paste a corrisponing number into C1 for example their values would be ("PV"=1 "PR"=2 "WV"=3 "WR"=4 "TV/CV"=5 "AL"=6 "Other"=7 "TT"=8)

End result would look like this;

6141GMPV 6141GM 1

If I cant do it directly and need to layer the process I dont mind, for instance using a formula to split the last 2 characters then perhaps If'ing or ISERROR'ing them to get a number output etc. Obviously the more info the better but i'd be happy to just have someone to help me built the method at least?

Hope you guys get the picture? If not give me a shout

[StephenR]

Maybe this will start you off:

Please Login or Register  to view this content.

[James_C]

Thanks alot for your replt Stephen, I copied your code into a fresh workbook and set up the scenario I described but sadly all that happens when I run the Macro is that A1 is merely copied to B1, the "pv" is still present at the end...... Any ideas? Also the A1 B1 C1 thing is just scenario, realisticaly A1 would be an ActiveCell that would change depending on the length of a list and B1 and C1 would be on a different workbook. But lets not worry about that for now. Would be great if we could just get the basics working... My knowledge is limited but I'm very interested in how it all works, managed to teach myself a few bits of code just from reading over ones I had recorded. As I understand Dim is used to specify variables? dunno about String and str and all that but I am to get it all one day thanks once again for your reply - James

[StephenR]

James - it works for me, and I can only go on what you say. You said you wanted it copied from A1 to B1. If you need something more complicated please describe in more detail and attach a small workbook illustrating this.

[James_C]

Ok I've put together a sheet for you Stephen. I managed to edit some formula code from a previous post to achieve the attached results - I hope it explains everything properly? I just want it all done in a macro if poss and not using work space on the sheets concerned. Thanks for your time - James

[StephenR]

James - try this. It operates on the active cell.

Please Login or Register  to view this content.

[James_C]

Thanks for that Stephen, I'm gonna have to play around with it a little to get it to "paste" to other workbooks but thats no real problem .... Thanks for your help on this , and sorry its taken so long for me to reply ..... - James