Replace in VBA Excel

Discussion in 'Microsoft Excel Programming' started by Maurice, Jul 15, 2003.

  1. Maurice

    Maurice Guest

    In Visual basic you can use the Replace function to remove
    unwanted characters. What would you use in VBA tom
    accomplish the same. When I try to use the replace
    function, it gives an error "Sub or function is not
    defined"
    Maurice
     
    Maurice, Jul 15, 2003
    #1
    1. Advertisements

  2. Maurice

    Chip Pearson Guest

    Maurice,

    Replace was added to VBA in version 6, or Excel 2000, so it is not
    available in earlier versions. In its place, you can use

    Result = Application.WorksheetFunction.Substitute(...)

    to call the SUBSTITUTE worksheet function from within VBA. See
    help in Excel for SUBSTITUTE for syntax details.


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com




    "Maurice" <> wrote in message
    news:0ccb01c34adf$49ce86e0$...
    > In Visual basic you can use the Replace function to remove
    > unwanted characters. What would you use in VBA tom
    > accomplish the same. When I try to use the replace
    > function, it gives an error "Sub or function is not
    > defined"
    > Maurice
     
    Chip Pearson, Jul 15, 2003
    #2
    1. Advertisements

  3. Maurice

    Maurice Guest

    Thanks,
    That did it for me!!!!
    Maurice

    >-----Original Message-----
    >Maurice,
    >
    >Replace was added to VBA in version 6, or Excel 2000, so

    it is not
    >available in earlier versions. In its place, you can use
    >
    >Result = Application.WorksheetFunction.Substitute(...)
    >
    >to call the SUBSTITUTE worksheet function from within

    VBA. See
    >help in Excel for SUBSTITUTE for syntax details.
    >
    >
    >--
    >Cordially,
    >Chip Pearson
    >Microsoft MVP - Excel
    >Pearson Software Consulting, LLC
    >www.cpearson.com
    >
    >
    >
    >
    >"Maurice" <> wrote in message
    >news:0ccb01c34adf$49ce86e0$...
    >> In Visual basic you can use the Replace function to

    remove
    >> unwanted characters. What would you use in VBA tom
    >> accomplish the same. When I try to use the replace
    >> function, it gives an error "Sub or function is not
    >> defined"
    >> Maurice

    >
    >
    >.
    >
     
    Maurice, Jul 15, 2003
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Replace method - cannot find any data to replace

    Guest, Apr 4, 2006, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    876
    Mark Lincoln
    Apr 6, 2006
  2. Guest

    Using Find and Replace to replace " in a macro

    Guest, Apr 13, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    541
    dok112
    Apr 13, 2006
  3. Mike

    Replace text with variable using VBA replace code?

    Mike, Nov 9, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    6,475
    Guest
    Nov 9, 2006
  4. Joe HM

    Find/Replace Event or Find/Replace for Protected Sheet ...

    Joe HM, Oct 27, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    1,085
    Joe HM
    Oct 27, 2007
  5. Forgone

    VBA Macro to open excel file and replace VBA module with update

    Forgone, Apr 8, 2010, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    6,976
    Forgone
    Apr 8, 2010
Loading...

Share This Page