Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Macro for changing names
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel Macro for changing names
Resolved · Low Priority · Version 2010
Reply Thu 27 Oct 2011, 14:43Delegate Gavin said...
Gavin has attended:
Excel Advanced course
Excel Macro for changing names
Hello,
In one column of a table I have Departmental Names.
"Drama" has now become "Theatre and Performance".
So each week I need to:
filter for "Drama"
type in "Theatre and Performance"
Drag this into all filtered cells below.
How would would I automate this as the number of "Drama"s returned when filtered will differ each week? i.e. the table will increase with time.
Thanks for your help,
Gavin
For upcoming training course dates see: Pricing & availability
RE: Excel Macro for changing names
Hello Gavin,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding changing names.
I would use the 'Find and Replace' method which will work for the entire sheet. You can create a macro to do this automatically each time it is required.
Of course, if there are other instances of 'Drama' in other columns that shouldn't change, then a little VBA coding will be needed.
Try the 'Find & Replace' method and see if this works for you.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Reply Wed 2 Nov 2011, 09:46Delegate Gavin said...
RE: Excel Macro for changing names
Hello Rodney,
Thanks for this.
Unfortunately, the word "Drama" will crop up in another column for the course titles.
Gavin
RE: Excel Macro for changing names
Hello Gavin,
Yes, the solution I gave you won't work if there are other columns containing the word 'Drama', so what I suggest is that you create a macro that follows the exact steps you outlined, except when you copy the cell which has replacement text down to the filtered cells below, make sure that you don't drag on the bottom right fill-handle... simply double click it which then copies all the way to the bottom cell.
Whenever you run the macro it will automatically include all the items in the filtered list. Your macro should also include clearing the filter after the name change has been done.
Give this a try!
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Tue 8 Nov 2011: Automatically marked as resolved.
|