+ Reply to Thread
Results 1 to 5 of 5

Find & Replace Not Working in VBA

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Find & Replace Not Working in VBA

    I have been trying to add code to an existing macro that finds all cells that have the same set of characters in them and remove said characters.

    If the cell contents is $30.000 **, I want to remove the extra space after the last zero and the two asterisks. I can do it using Find and Replace while in the worksheet itself using Find "~**" and Replace "". Everything works fine. I tried recording this as a macro and pasting it into my code and it just will not find or replace cells that have the asterisks in them.

    This is the code the macro is capturing:
    Please Login or Register  to view this content.
    What am I missing here?

    I am running 2007 but this needs to work with 2003, too.

    Thanks for your help!

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find & Replace Not Working in VBA

    Its seems to work for me? I add some data Like Testing1 ***,Testing2 ***,Testing3 *** ect... and it removed all *.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find & Replace Not Working in VBA

    Hi,

    Are you sure the cells contents contain those characters or is it the number 30 that has been formatted with the custom format
    [$$]#,##0.000 "**"

    If the latter then the ** don't exist in the real sense and hence a Find won't find them.

    Just change the formatting and lose the "**"
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    08-16-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find & Replace Not Working in VBA

    There must be another line of code further up in the macro that is messing it up. I'll have to go do some digging and see what may be overriding this section of the code.

  5. #5
    Registered User
    Join Date
    08-16-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find & Replace Not Working in VBA

    Quote Originally Posted by jazzy1 View Post
    There must be another line of code further up in the macro that is messing it up. I'll have to go do some digging and see what may be overriding this section of the code.
    Richard gave me a hint of what to look for. I moved the code up in the macro before the formatting and now it is working as desired. Thanks Mike & Richard!
    Last edited by Cutter; 08-16-2012 at 09:53 PM. Reason: Removed double post

+ 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