+ Reply to Thread
Results 1 to 8 of 8

Convert formula to a Macro

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Convert formula to a Macro

    I'm using this formula =IF(LEFT(A1,3)="The",RIGHT(A1,(LEN(A1)-4))&" (The)",A1) to convert any string starting with 'The' as follows:

    The Long and Winding Road
    Tired Feet
    The Highest Mountain

    to

    Long and Winding Road (The)
    Tired Feet
    Highest Mountain (The)

    I want to be able to convert this into a macro, but I'm not sure how to go about doing that.

    Can anyone help, my knowldge of VBA is extremely limited.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,053

    Re: Convert formula to a Macro

    Select the cell with the formula, turn on the macro recorder, press F2 and the enter.

    Turn off of the macro recorder and the see what you have.
    Last edited by jeffreybrown; 12-14-2012 at 07:03 PM.
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Homeless. I am not kidding.
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,134

    Re: Convert formula to a Macro

    Use UDF

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Convert formula to a Macro

    Hi JieJenn,

    That works well thank you, though what I really want to be able to do is to run a Macro rather than a UDF, so I don't have to enter the function in a second column. I want to be able to run a macro over the column containing the source text and for the macro to amend the text accordingly without having to insert a new column and then copy and paste special the new text as a value, if that makes sense. I have a large worksheet and I am trying to avoid having to 'move' any data where possible.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,902

    Re: Convert formula to a Macro

    This updates data in Col.A
    Please Login or Register  to view this content.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Convert formula to a Macro

    Hey HangMan,

    You can run Jie Jenn's Function as a macro:
    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  7. #7
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Convert formula to a Macro

    Hi jindon,

    That is absolutely perfect and does exactly what I need it to.

    Many thanks for your help

  8. #8
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Convert formula to a Macro

    Hi xladept,

    Okay, I didn't realise you could run functions as Macro's. I'll give that a try as well.

    Many thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1